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).