EXPLAIN EXTENDED

How to create fast database queries

Archive for September 18th, 2009

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

with 29 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
)

Finally, it's MySQL time.

As always, we will create the sample tables:

Read the rest of this entry »

Written by Quassnoi

September 18th, 2009 at 11:00 pm

Posted in MySQL