Re: New join type ?

"Ben McIntyre" <connectmmben_at_netscape.net> wrote in message news:a9d4de17.0203182108.41c05d0f_at_posting.google.com...
>
> 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.

Did you try writing out the coalesce, because that is probably why the query optimizer doesn't find the efficient solution here. You could rewrite it to: "table1.col1 IS NULL OR table1.col1 = 0". If that doesn't work try helping the poor thing a little by telling it that it is actually a UNION that you want: :-)

SELECT *
FROM table1.col1, table2.col1
WHERE NOT ( table1.col1 = 0) AND (table1.col1 = table2.col1);

UNION SELECT *
FROM table1.col1, table2.col1
WHERE table1.col1 = 0 OR table1.col1 IS NULL;

• Jan Hidders