EXPLAIN EXTENDED

How to create fast database queries

Archive for September 17th, 2009

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle

with 7 comments

Which method is best to select values present in one table but missing in another one?

This:

SELECT  l.*
FROM    t_left l
LEFT JOIN
t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

, this:

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
(
SELECT  value
FROM    t_right r
)

or this:

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    t_right r
WHERE   r.value = l.value
)

Today, we will see how Oracle copes with these queries.

And to do this, we, of course, should create sample tables:

Read the rest of this entry »

Written by Quassnoi

September 17th, 2009 at 11:00 pm

Posted in Oracle