Re: New join type ?

connectmmben_at_netscape.net (Ben McIntyre) wrote in message news:<a9d4de17.0203182108.41c05d0f_at_posting.google.com>...

> Am in the thick of some quite complex SQL work (MS SQL 2000). One
> issue that keeps coming up is where I have a column which will match a
> column in another table under two circumstances :
> 1. It is null or 0, in which case it should match any value in the
> other table
> 2. It is not null or 0, in which case it should match only identical
> values in the other table
>
> Of course this is easy to do in SQL with
> SELECT * FROM table1.col1, table2.col1
> WHERE (table1.col1 = table2.col1) OR coalesce(table1.col1, 0) = 0

Typo: SELECT * FROM table1.col1, table2.col1

---------------------------^^^^---------^^^^

> However it has been my experience that once an OR is put into a join
> criteria, the SQL engine thrashes as it grinds out the boolean logic
> for every row.

Wrong: expanding ORs into concatenation is one of trivial transformations that optimiser does. If you are not satisfied with query performance index it: you might want to consider functional index on 'coalesce'.  

> This match type occurs all over my database, usually with several
> 'normal' columns which form the primary join, then these additional
> 'wildcard' columns.

Do you want all the rows from table2 when there is no match? Then you end up with a union like Jan describe, where you have a cartesian product in a second operand. It's not going to be efficient, anyway, if you want all rows from the result set.

Normally, you join a row with a row with nulls: this is called outer join.

Outer join discriminates between all values and null, and in your case you want to distingish NULL and 0 with everything else. A view

create view v1 as
  select CASE WHEN col1=0 THEN NULL ELSE col1 END, table1.*   from table1

effectively reduces the set {NULL, 0} to just {NULL}.

> My question is, wouldn't it be a good idea to invent a new special
> column type, call it a 'wild', similar to 'null', which is its own
> special type, but which matches anything in a similar way that null
> matches nothing.

It remains to be seen that your problem is not expressed in standard SQL.  
> Because the null is a native column type, the logic for its use in
> join expressions is built in at a much lower level in the database
> engine than mere boolean logic, and a wild value could be handled in a
> similar way, having the effect of greatly speeding up this particular
> kind of join operation.

What? NULL is a type?

Also, I wouldn't put forward assumptions like that unless you are very familiar with database internals.  

> Obviously, there would need to be rules regarding use of this 'wild'
> value in joins, but this is not a particularly difficult task.

Depending upon the rules, this can be a very difficult task.  

> Has anyone heard of anything like this ? Can PostgreSQL be customised
> to do this, either by the user or through source code (with reasonable
> ease) ?
> Am I just missing something regarding efficient SQL writing or DB
> tuning, and can the above SQL be run efficiently, in which case this
> is all just hot air ?

In theory, yes, databases today allow user-defined types. In practice, there are significant limitations and a steep learning curve for user-defined types developer. In oracle, spatial and text are the only user-defined types I'm aware of. Customer success stories for other databases anybody? Received on Tue Mar 19 2002 - 23:17:47 CET