I have two tables (sample below) with some additional columns that I have not shown here. The only way to join the two tables is by using a combination of first name, last name, and address.

table A (~3000 rows):

table B (~ 9M rows):

I have tried the following code -

select * from 
(select first_name, last_name, address, concat(first_name, last_name, address) as con_A
from table_A) as A
join
(select first_name, last_name, address, concat(first_name, last_name, address) as con_B
from table_B) as B
on A.con_A=B.con_B

The above code is a generalization of what my code looks like. I have tried to only put the columns I need in the sub queries in my original code.

The two sub queries run within seconds when I run them individually but taking over an hour to execute when I join them.

CodePudding user response：

I don't know that I'd use inline tables for this ... why not just a direct join?

select
    A.first_name,
    A.last_name,
    A.address
from
    table_A A
    join table_B B on A.first_name = B.first_name AND
    A.last_name = B.last_name AND
    A.address = B.address

Now this is an inner join, so you'll only get exact matches for both. If you want to show records from one table whether they match or not, you'll need to use an outer join (left or right depending on the table you want to drive the results).

CodePudding user response：

Instead of using the sub query in join you can directly use the join for better performance of the query.

select A.first_name, A.last_name, A.address from 
(select first_name, last_name, address from table_A) as A
join
(select first_name, last_name, address from table_B) as B
on A.first_name=B.first_name, A.last_name=B.last_name, A.address=B.address

And one more thing don't use normal join use either left or right join based on your need. If you joining 3k records with 9M records all combination will form in the result. It makes very cost effective operation.