Exists — SQL Technical Interview Questions & Answers

Exists

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.

Technical Interview Tip: WHERE EXISTS (SELECT 1 FROM marks m WHERE m.roll = s.roll);

Code Examples for Exists

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.

How to Explain Exists to an Interviewer

When do you use EXISTS?

‘Is there any matching child row?’ NOT EXISTS for none