> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and (tableA.value=tableB.value or
> (tableA.value IS NULL AND tableB.value IS NULL));
>
> It's possible that won't use an index, either, due to the OR, in which
> case you could try a union between a select with is-null only, and
> another with equality, something like:
>
> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and tableA.value=tableB.value union
> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS
> NULL;
Even though this query has no OR operation:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value
it has to do a complete pass over one of the tables
anyway since it does not have a constrained value (or at
least a full pass over an index). So I think in this case
the first query with the OR listed above is more efficient
since it would do just a single pass over one of the tables
instead of 2 passes (one for each SELECT in the UNION).
I've been thinking of heuristics to rewrite SELECTs with ORs
using UNIONs, which is pretty straightforward, but knowing
when not to perform this optimization is the tricky part.
____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search
that gives answers, not web links.
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
