Subqueries & CTEs
Concept  ·  SQL & Querying

EXISTS tests for rows, never values — its SELECT list is never evaluated, so SELECT 1, SELECT *, and even SELECT 1/0 are identical.

EXISTS finds at most one row and stops (the semi-join short-circuit that makes it fast). Since no value is returned, none is computed: the select list is syntax. It retires two superstitions: the micro-optimizer's SELECT 1 'for performance' (harmless) and the fear that SELECT * 'reads all columns' (it reads none). Where the list does matter is the neighbours — IN returns and compares values (NULL trouble), scalar subqueries return values.

See it break
members
m
m1
m2
badges
m
m1
The trap
SELECT
  m
FROM
  members
WHERE
  EXISTS (
    SELECT
      1 / 0
    FROM
      badges b
    WHERE
      b.m = members.m
  )
ORDER BY
  m;
The fix
SELECT
  m
FROM
  members
WHERE
  EXISTS (
    SELECT
      1
    FROM
      badges b
    WHERE
      b.m = members.m
  )
ORDER BY
  m;
The trap
m1
The fix
m1

The feared SELECT 1/0 inside EXISTS returns the same members and never errors — EXISTS tests for rows, so its SELECT list is never evaluated; SELECT 1, SELECT *, and SELECT 1/0 are identical, and the division never runs.

The rule

Write whichever list your team's style prefers; spend review attention on the WHERE correlation instead.

Shows up elsewhere
Anti-join three waysNOT IN meets NULL
Try one →Prove it in practice →