Geek Answers Handbook

AND / OR statements in SQL JOIN

I have two tables: table1 and table2 . I can join them using id1 or id2 . I prefer to use id1 , but as in some lines id1 missing, so I have to use id2 . Is the following syntax specified correctly:

 SELECT * FROM table1 as a LEFT JOIN table2 as b ON (a.id1 is not null and a.id1 = b.id1) or (a.id2 is not null and a.id2 = b.id2)

It returns some results, but I want to be sure whether it is valid, since I have not seen it before.

Are there any better ways to do this?

+5
source share
1 answer

It seems that you have a decent answer in the comments, but to throw one more opportunity in the ring, you can run both requests and combine them.

 select * from table1 as a LEFT JOIN table2 as b on a.id1 = b.id1 union select * from table1 as a LEFT JOIN table2 as b on a.id2 = b.id2

The union will remove any duplicates between sets and return records where any condition is true, just like yours or operator. Performance is wise, combining is probably a bit slower, but gives you easier control over sets. For example, if you want set 2 to return results when id1 is null, just add it to the where clause. Anyway, hope this helps.

+2
source

More articles:


All Articles