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?

Kalpa

Your title says you want to try JOINing on one column and if there isn't a match, to JOIN on a different column, but your question says you want to use both columns in a single join condition. Which of the two are you trying to do? – Stefanstefanac

Are the tables suitably indexed? Are the index statistics up to date? – Camp

Thank you for pointing that out. I am trying to do what the title says join on one column and if there isn't a match look to another to match on. I am attempting to do so with the example above which may not be the most effective way to do so. – Kalpa

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.

Camp

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

Pacifically

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   

Tomsk

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

Opah