IN or EXISTS: which writing to use?

Are you testing a list or a relationship?

statut IN ('Paid', 'Pending') describes a closed list. EXISTS is suitable when an exterior row must be preserved if a linked row meets a condition.

EXISTS avoids duplicating a customer that has multiple matching orders, because only the existence of the first match counts.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

SELECT c.id_client, c.nom
FROM clients c
WHERE EXISTS (
  SELECT 1
  FROM commandes co
  WHERE co.id_client = c.id_client
    AND co.statut = 'Paid'
);

Read the result like an analyst

The subquery is correlated by id_client. SELECT 1 indicates that internal columns are not used in the result.

Be careful with NOT IN if the list may contain NULL; NOT EXISTS often expresses the absence of a relationship more clearly.

• Identify the correlation key.
• Test a case with multiple matches.
• Test an unmatched case.
• Check for the possible presence of NULL.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Showing internal columns when only the existence matters.
• Forget the correlation and make EXISTS true for all rows.
• Use NOT IN with unanticipated NULL.
• Join then add DISTINCT to compensate for repetitions.

Get into practice

Do exercise EXISTS on large orders, then change the threshold and observe the retained population.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.