CODEWITHSUNDEEP
sqloracle-databaseleft-join
dscl
dscl

Reputation: 1626

Best way to do multiple left outer excluding joins

I have one table that I need to bump against multiple tables with left outer joins excluding the right(s). Is there a best practice for this? Union all the other tables first? Something else?

Here's the first thought that comes to my mind to handle this, but I want to know if there is a better more efficient way.

select
    master_table.*
from
    master_table
left outer join
    (
        select customer_id from table_1
        union
        select customer_id from table_2
        union
        select customer_id from table_3
        union
        select customer_id from table_4
    ) bump_table
on
    master_table.customer_id = bump_table.customer_id
where
    bump_table.customer_id is null

Upvotes: 2

Views: 1475

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658082

The basic form is surely faster - similar to the NOT EXISTS that @dbenham already supplied.

SELECT m.*
FROM   master_table m
LEFT   JOIN table_1 t1 ON t1.customer_id =  m.customer_id
LEFT   JOIN table_2 t2 ON t2.customer_id =  m.customer_id
LEFT   JOIN table_3 t3 ON t3.customer_id =  m.customer_id
LEFT   JOIN table_4 t4 ON t4.customer_id =  m.customer_id
WHERE  t1.customer_id IS NULL
AND    t2.customer_id IS NULL
AND    t3.customer_id IS NULL
AND    t4.customer_id IS NULL;

Upvotes: 2

dbenham
dbenham

Reputation: 130899

I should think a NOT EXISTS would be better. It certainly better communicates the intent of the query.

select * from master_table m
 where not exists( select 1 from table_1 where m.customer_id=table_1.customer_id)
   and not exists( select 1 from table_2 where m.customer_id=table_2.customer_id)
   and not exists( select 1 from table_3 where m.customer_id=table_3.customer_id)
   and not exists( select 1 from table_4 where m.customer_id=table_4.customer_id)

Upvotes: 2

Related Questions