LEFT JOIN and
NOT EXISTS equally, using same execution plan for both of them (namely a
Hash Anti Join for the example above).
NOT IN, which is semantically different since its logic is trivalent and it can return
NULL, PostgreSQL tries to take this into account and limits itself to using a filter against a
hashed subplan for a hashable resultset like in example above).
Since it need to search the hash table for each missing value twice (first time to find the value, second time to find a
NULL), this method is a little less efficient.
SELECT l.* FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL
SELECT l.* FROM t_left l WHERE l.value NOT IN ( SELECT value FROM t_right r )
SELECT l.* FROM t_left l WHERE NOT EXISTS ( SELECT NULL FROM t_right r WHERE r.value = l.value )