Wednesday, November 30, 2011

Using an outer join instead of the "not in" clause

PROBLEM:
Why to use an outer join instead of the "not in" clause?

SOLUTION:
If you want to get all of the records in the ADDRESSES table that did not have a valid foreign key reference to CUSTOMERS table (i.e. orphaned records) you could write the query the way your mind would normally think about: give me all the records in ADDRESSES where is not a join to CUSTOMERS. Logically you would write the query like this:


SELECT a.address_id
FROM addresses a
WHERE a.customer_id NOT IN
    (SELECT c.customer_id
     FROM customers c);

Unfortunately this method is extremely slow. A much faster way to do it is like this:


SELECT a.address_id
FROM customers c, addresses a
WHERE c.customer_id = a.customer_id (+)
AND c.customer_id IS NULL;

What this query is doing is giving you all the records in ADDRESSES where the join to CUSTOMERS fails. Much faster!


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

No comments:

Post a Comment