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'

)