SQL · Theory

Subquery

Theory

278/439

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.

Exam tip

WHERE marks = (SELECT MAX(marks) FROM students);

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.

Short notes

DefSELECT inside SELECT. Example: MAX in WHERE.
RuleOne value vs a list (IN). Derived tables need aliases.
TrapMulti-row subquery where one value is required.

SQL · Theory

Subquery

← All stacks

Theory

278/439

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.

Exam tip

WHERE marks = (SELECT MAX(marks) FROM students);

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.

Short notes

DefSELECT inside SELECT. Example: MAX in WHERE.
RuleOne value vs a list (IN). Derived tables need aliases.
TrapMulti-row subquery where one value is required.

Subquery

Subquery — sample query

Short notes

Questions

Subquery

Subquery — sample query

Short notes

Questions