Use IN and EXIST in the Right Situations

September 15, 2022

IN is efficient when most of the filter criteria is in the subquery. EXISTS is efficient when most of the filter criteria is in the main query.

Don’t do this:

SELECT *
FROM users AS u
WHERE id IN (SELECT user_id FROM address)

Instead do this:

SELECT *
FROM users AS u
WHERE EXISTS (
          SELECT * 
          FROM address AS a
          WHERE a.user_id = u.user_id
         )