Explain Extended: SQL Explained

This series of articles is inspired by multiple questions asked by the site visitors and Stack Overflow users, including Tony, Philip, Rexem and others.

Which method (NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL) is best to select values present in one table but missing in another one?

This:

view sourceprint?
1.SELECT  l.*
2.FROM    t_left l
3.LEFT JOIN
4.t_right r
5.ON      r.value = l.value
6.WHERE   r.value IS NULL

, this:

view sourceprint?
1.SELECT  l.*
2.FROM    t_left l
3.WHERE   l.value NOT IN
4.(
5.SELECT  value
6.FROM    t_right r
7.)

or this:

view sourceprint?
1.SELECT  l.*
2.FROM    t_left l
3.WHERE   NOT EXISTS
4.(
5.SELECT  NULL
6.FROM    t_right r
7.WHERE   r.value = l.value
8.)