SQL: Speed Improvement - Left Join on cond1 or cond2

Question

SELECT DISTINCT  a.*, b.*
FROM             current_tbl a
LEFT JOIN        import_tbl  b 
                 ON ( a.user_id = b.user_id 
                   OR ( a.f_name||' '||a.l_name = b.f_name||' '||b.l_name)
                 )

• Two tables that are basically the same
• I don't have access to the table structure or data input (thus no cleaning up primary keys)
• Sometimes the user_id is populated in one and not the other
• Sometimes names are equal, sometimes they are not

I've found that I can get the most of the data by matching on user_id or the first/last names. I'm using the ' ' between the names to avoid cases where one user has the same first name as another's last name and both are missing the other field (unlikely, but plausible).

This query runs in 33000ms, whereas individualized they are each about 200ms.

• I've been up late and can't think straight right now
• I'm thinking that I could do a UNION and only query by name where a user_id does not exist (the default join is the user_id, if a user_id doesn't exist then I want to join by the name)
• Here is some free points to anyone that wants to help

Please don't ask for the execution plan.

Answer 1

Looks like you can easily avoid the string concatenation:

OR ( a.f_name||' '||a.l_name = b.f_name||' '||b.l_name)

Change it to:

OR ( a.f_name = b.f_name AND a.l_name = b.l_name)