patternsqlModerate
Join on multiple columns with OR
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:
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
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.col1and 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.