Subquery — SQL Technical Interview Questions & Answers

Subquery

Subquery

  • A subquery is a SELECT inside another SQL. WHERE marks = (SELECT MAX(marks) FROM students); the topper. The inner query runs as a value or as a table.
  • Scalar subquery returns one value. IN (SELECT roll FROM …) returns a list. FROM (SELECT …) AS t is a derived table and needs an alias.
  • Correlated subquery uses the outer row (WHERE m.roll = s.roll) and can be slow. A JOIN is often clearer and faster.
  • Trap: subquery that returns two rows where one value was expected. Trap: correlated subquery in a huge table without an index.
  • On the example next to this theory: Subquery — uNION merges result sets and drops duplicate rows.

Technical Interview Tip: WHERE marks = (SELECT MAX(marks) FROM students);

Code Examples for Subquery

Subquery — 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;

Subquery — uNION merges result sets and drops duplicate rows.

How to Explain Subquery to an Interviewer

Give a subquery example.

Topper: marks = (SELECT MAX(marks) FROM students)

JOIN vs subquery?

JOIN often clearer/faster Subquery fine for one value