EXISTS versus IN

Use (NOT) EXISTS instead of (NOT) IN whenever you can.

Background

Analyze cases where you only want a Yes/No answer (do records meet a particular condition or not?).

If you can use it, (NOT) EXISTS is always faster than (NOT) IN, because searching stops as soon as a record is (not) found.

(NOT) IN causes records to be fetched and stored temporarily before the main query can be evaluated.

Example

Consider the business rule: "A reservation must have a participant with an insurance". Rewrite an SQL statement like the following:

SELECT    '' violation
FROM      reservation r
WHERE     r.res_id NOT IN
(
          SELECT    pt.res_id
          FROM      participant pt
          WHERE     pt.res_id = r.res_id
          AND       pt.insurance = 'Y'
)

to:

SELECT    '' violation
FROM      reservation r
WHERE NOT EXISTS
(
          SELECT    ' '
          FROM      participant pt
          WHERE     pt.res_id = r.res_id
          AND       pt.insurance = 'Y'
)