AND/OR statements in SQL JOIN

Question

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

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 if it is valid as I haven't seen it used before.

Are there better ways to do this?

Answer 1

Looks like you have a decent answer in the comments, but to toss another possibility into the ring, you could run both queries and union 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 eliminate any duplicates between the sets, and will return records where either condition is true, much like your or statement. Performance wise, the union is probably a little slower, but gives you easier control over the sets. For instance if you only want set 2 to return results when id1 is null, just add it to the where clause. Anyway hope that helps.