EXISTS versus IN

See Also

Tip

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')