rocknbil - 4:02 am on Sep 11, 2010 (gmt 0)
But if it's not in table (the first table), that query won't return any results.
Unless I'm missing something, that's not true. One of the advantages of a left join is that is will return results whether or not there's a matching record in the joined table. so
select * from table left join table 2 on table.id=table2.rid=table1.rid
where table1.field='val' or table2.field='val'
even if there's no "table.id=table2.rid=table1.rid" - that is, a null on the join - it would return a row id a found record is in table 1. If there's no found record in table 1 but there *is* one in table2, it will return a record.
I have always been under the impression that using UNION is faster and can be considered optimized if you use it over a bunch of OR statements.
I can't contest this with any real evidence, but a nested select is still another select. :-) Any select has to select from the entire table, an or . . should not.