New join type ?

Hi all

This IS a theory question/suggestion, but please bare with me while I explain the issue.

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

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.

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

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.

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.

Obviously, there would need to be rules regarding use of this 'wild' value in joins, but this is not a particularly 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 ?

Ben McIntyre
Darwin, Australia
benmc.nojamorspam_at_bigfoot.thankyoumam.com Received on Mon Mar 18 2002 - 23:08:54 CST