Re: New join type ?
Date: Tue, 19 Mar 2002 14:12:33 +0100
Message-ID: <3c9738d6$1_at_news.uia.ac.be>
"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 *
UNION
FROM table1.col1, table2.col1
WHERE NOT ( table1.col1 = 0) AND (table1.col1 = table2.col1);
FROM table1.col1, table2.col1
WHERE table1.col1 = 0 OR table1.col1 IS NULL;
- Jan Hidders