Joining on one column and if no match join on another

Question

I am attempting to use multiple columns in my join like this:

FROM Table1 t
INNER JOIN Table2 s ON t.number = s.number OR t.letter = s.letter

Both of these tables have several hundred thousand rows of data and it is running infinitely.

Any ideas?

Answer 1

You mean something like:

FROM Table1 t
INNER JOIN Table2 s ON case
  when t.number = s.number then 1
  when t.letter = s.letter then 1
  else 0 end = 1

The first matching condition wins.

Answer 2

One possibility is to use left join and fix the rest of the query:

FROM Table1 t LEFT JOIN
     Table2 sn
     ON t.number = sn.number LEFT JOIN
     Table2 sl
     ON t.letter = sl.letter and sn.number is null

For performance, you want indexes on Table2(number) and Table2(letter).

Answer 3

ORs are usually produce bad performance. I would go for:

SELECT *
FROM Table1 t
       INNER JOIN Table2 s ON t.number = s.number 
UNION
SELECT *
FROM Table1 t
     INNER JOIN Table2 s ON t.letter = s.letter   

Answer 4

Simply using the OR keyword between your ON conditions works well for me, and it's easier to understand for developers:

SELECT * FROM table1 
JOIN table2 
ON
(table1.column_to_join = table2.column
OR
table1.other_column_to_join = table2.column