HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Join on multiple columns with OR

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
multiplewithcolumnsjoin

Problem

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.

Solution

I don't think there is a way to get this query to perform well, but the code will be easier to read if you use the IN predicate:

select * 
from tbl1 t1 
join tbl2 t2 
    on t1.col1 in (t2.col1, t2.col2, ...) 
    or ...
    or t1.col5 in (t2.col1, ... )

Code Snippets

select * 
from tbl1 t1 
join tbl2 t2 
    on t1.col1 in (t2.col1, t2.col2, ...) 
    or ...
    or t1.col5 in (t2.col1, ... )

Context

StackExchange Database Administrators Q#122756, answer score: 10

Revisions (0)

No revisions yet.