SQL Server – Join on Multiple Columns with OR Condition

join;sql server

I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ID number) in table 1 and 60 columns () in table 2 that must be used as joining keys. So, my code would be something like below:

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col1 OR 
t1.col1 = t2.col2 OR
t1.col1 = t2.col3 OR
...
t1.col5 = t2.col1

and so forth. The combination of 5 and 60 is huge and it kills the server. It also does not sound logical. I was thinking of putting 60 columns as row and increase the number of records for reducing the number of column. However, not yet sure if this is the best solution. Any solution that does not kill server is highly appreciated.

Note: Kindly note that each column from tb1 is to be matched against 10-15 columns in tbl2. For instance, column col1 that contains "Account Number" is joined with col1-10 which hold all potential "Account Number". col1 in no way is joined with email address or some other columns.

Best Answer

While I think you should consider changing the setup, you could try the UNION approach.

Instead of making multiple OR joins, you can make specific queries per case, and then UNION them together. Then you have the chance to make each individual query use indexes.

So something like

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col1 

UNION 

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col2

.....

UNION 

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col5 = t2.col1

and so forth.....

Doing that, I'd also consider only selecting the key from the table instead of *; and into a temporary structure. From that select the key and join and select with your data, so you can minimize the size of each individual index for the UNION-queries.

(But - you should seriously consider changing the structure in my view)

Related Question