SQL · Theory

Exists

Theory

53/439

Exists

EXISTS (subquery) is true if the subquery returns at least one row. SELECT name FROM students s WHERE EXISTS (SELECT 1 FROM marks m WHERE m.roll = s.roll AND m.score < 40); students who have at least one fail paper.

SELECT 1 inside is a habit — you only care that a row exists, not the columns. NOT EXISTS is ‘no matching child row’.

Often equivalent to a JOIN, but EXISTS stops at the first match. Nice for ‘is there any’.

Trap — EXISTS (SELECT * FROM marks) without linking to the outer row — true for everyone if marks is non-empty.

On the example next to this theory — Exists — uNION merges result sets and drops duplicate rows.

Exam tip

WHERE EXISTS (SELECT 1 FROM marks m WHERE m.roll = s.roll);

Exists — sample query

CREATE TABLE a (id INTEGER, label TEXT);
CREATE TABLE b (id INTEGER, label TEXT);
INSERT INTO a VALUES (1, 'x'), (2, 'y');
INSERT INTO b VALUES (2, 'y'), (3, 'z');
SELECT label FROM a
UNION
SELECT label FROM b;

Exists — uNION merges result sets and drops duplicate rows.

Short notes

DefEXISTS is true if the subquery finds a row.
RuleCorrelate with the outer table (m.roll = s.roll).
TrapUncorrelated EXISTS that is true for all rows.

SQL · Theory

Exists

← All stacks

Theory

53/439

Exists

SELECT 1 inside is a habit — you only care that a row exists, not the columns. NOT EXISTS is ‘no matching child row’.

Often equivalent to a JOIN, but EXISTS stops at the first match. Nice for ‘is there any’.

Trap — EXISTS (SELECT * FROM marks) without linking to the outer row — true for everyone if marks is non-empty.

On the example next to this theory — Exists — uNION merges result sets and drops duplicate rows.

Exam tip

WHERE EXISTS (SELECT 1 FROM marks m WHERE m.roll = s.roll);

Exists — sample query

CREATE TABLE a (id INTEGER, label TEXT);
CREATE TABLE b (id INTEGER, label TEXT);
INSERT INTO a VALUES (1, 'x'), (2, 'y');
INSERT INTO b VALUES (2, 'y'), (3, 'z');
SELECT label FROM a
UNION
SELECT label FROM b;

Exists — uNION merges result sets and drops duplicate rows.

Short notes

DefEXISTS is true if the subquery finds a row.
RuleCorrelate with the outer table (m.roll = s.roll).
TrapUncorrelated EXISTS that is true for all rows.

Exists

Exists — sample query

Short notes

Questions

Exists

Exists — sample query

Short notes

Questions