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 25 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