Subqueries with IN

With a subquery, IN is an alias for = ANY and NOT IN for <> ALL, which is exactly why one NULL empties a NOT IN result.

Syntaxexpr [NOT] IN (SELECT col FROM tbl WHERE condition)

IN with a subquery tests membership against a column of values the server computes rather than a list you write out. It is the natural way to ask "is this row's key among the keys that came back from that other query?".

Shorthand and longhand

The MySQL 8.4 manual states that when used with a subquery, IN is an alias for = ANY, and that NOT IN is an alias not for <> ANY but for <> ALL. These pairs are identical:

WHERE s1 IN     (SELECT s1 FROM t2)   -- shorthand
WHERE s1 = ANY  (SELECT s1 FROM t2)   -- longhand

WHERE s1 NOT IN (SELECT s1 FROM t2)   -- shorthand
WHERE s1 <> ALL (SELECT s1 FROM t2)   -- longhand

SOME is an alias for ANY.

The NULL trap

Reading NOT IN as <> ALL makes the trap obvious. The row survives only if it is unequal to every value returned. A NULL in that column makes one of those comparisons UNKNOWN, so the chain can never evaluate to TRUE, and WHERE keeps only TRUE. A single NULL anywhere in the subquery result makes NOT IN return nothing at all. Plain IN is unharmed for rows that do match, because one TRUE is enough.

Restrictions

LIMIT is not permitted in a subquery to IN, ALL, ANY or SOME; you get ERROR 1235. Row constructors are supported — (a, b) IN (SELECT x, y FROM t) is legal — but (a, b) > ALL (subquery) is not.

Example

Example · sql
-- Customers who have placed at least one paid order.
SELECT c.id, c.name, c.email
FROM customers AS c
WHERE c.id IN (
        -- IN is = ANY: one matching value is enough, duplicates are harmless,
        -- and each customer still appears exactly once in the result.
        SELECT o.customer_id
          FROM orders AS o
         WHERE o.status = 'paid'
      )
ORDER BY c.name;

When to use it

  • Select parent rows that have at least one child matching a condition.
  • Restrict a report to the product IDs returned by a separate catalogue query.
  • Match on a composite key with a row constructor, such as (order_id, product_id).
  • Exclude a set of rows computed by another query, when that column is guaranteed NOT NULL.

More examples

The NOT IN NULL trap, and two fixes

This is the single most common subquery bug: the query returns an empty set and everyone assumes the data is wrong. It only surfaces once a NULL appears, which may be months after the query shipped. Prefer fix 2 as a habit, and keep NOT IN for columns declared NOT NULL.

Example · sql
-- order_items.product_id is nullable (legacy rows imported without a catalogue link).
-- If a single NULL is present, this returns ZERO rows, not 'products never ordered'.
SELECT p.id, p.name
FROM products AS p
WHERE p.id NOT IN (SELECT oi.product_id FROM order_items AS oi);

-- Fix 1: strip the NULLs so <> ALL can reach TRUE.
SELECT p.id, p.name
FROM products AS p
WHERE p.id NOT IN (SELECT oi.product_id FROM order_items AS oi
                    WHERE oi.product_id IS NOT NULL);

-- Fix 2, preferred: NOT EXISTS is unaffected by NULLs in the inner column.
SELECT p.id, p.name
FROM products AS p
WHERE NOT EXISTS (SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id);

Row constructors with IN

Use this whenever the key you are matching on has more than one column. Writing it as two separate IN subqueries is wrong: that matches any order in the set combined with any product in the set, not the pairs that actually occur together. Note that the row form works with IN and NOT IN, but MySQL 8.4 does not support it with ALL, ANY or SOME.

Example · sql
-- Which order lines duplicate a line that already exists in the archive?
-- (order_id, product_id) is compared as a unit, not column by column.
SELECT oi.order_id, oi.product_id, oi.qty
FROM order_items AS oi
WHERE (oi.order_id, oi.product_id) IN (
        SELECT a.order_id, a.product_id FROM archived_order_items AS a
      );

-- Equivalent longhand for a single known pair, showing the row constructor is
-- just ANDed equality: (a, b) = (1, 1) means a = 1 AND b = 1.
SELECT * FROM order_items WHERE (order_id, product_id) = (4711, 92);
SELECT * FROM order_items WHERE order_id = 4711 AND product_id = 92;

Working around the LIMIT restriction

You hit this as soon as you try to filter by a 'top N' list. The extra nesting level is not decoration: the inner derived table is materialised first, and the IN subquery then selects from a finished result, which is why the restriction no longer applies.

Example · sql
-- ERROR 1235: 'LIMIT & IN/ALL/ANY/SOME subquery' is not supported.
SELECT * FROM products
WHERE id IN (SELECT product_id FROM order_items ORDER BY qty DESC LIMIT 10);

-- Workaround: wrap it in a derived table. A derived table may carry ORDER BY
-- and LIMIT, and the IN subquery then just reads from it.
SELECT * FROM products
WHERE id IN (
        SELECT t.product_id
          FROM (SELECT product_id FROM order_items ORDER BY qty DESC LIMIT 10) AS t
      );

Related

sql insql existssql nullsql anti joinsql subquery vs join

Discussion

  • Be the first to comment on this lesson.