ConceptDBISAI-written · not yet proofread

Subquery

Also Subqueries

A subquery is a complete SELECT query in parentheses written where SQL expects a table, standing for the table that the inner query produces.

Know these firstSQL Query Structure

Definition

All operations on tables take one or more tables as arguments, and a SELECT query has a table as its result. Hence, wherever SQL expects a table (e.g. in FROM or as the right side of a JOIN), a whole SELECT query in parentheses can be written instead:

... FROM ( SELECT ... ) AS name ...

The subquery (inner query) stands for the table it computes; AS name names that table so the outer query can refer to its columns (name.col). The columns of the subquery’s table are its SELECT list, with the names given by AS.

Properties#

How to read a query with subqueries: understand the table created by each subquery, then read the outer query as if that table were stored under its name.
Typical use: per-group values back to rows. Grouping aggregates per column, so “for each year, the movie with the highest score” cannot be written with GROUP BY alone. Compute the aggregate per group in a subquery and join it back to the original table on the group key and the aggregate value (exam WS 23/24, Task 3.3). Ties yield several rows per group.
Typical use: filter groups, then join. A subquery with GROUP BY … HAVING … yields the keys of qualifying groups, which are joined with other tables (retake WS 23/24, Task 2.1.2).
Column names matter for NATURAL JOIN: a natural join with a subquery uses every column name the two share. Naming the aggregate MAX(score) AS score makes the join match on score as well, which is exactly what the argmax query needs; naming it max_score would join on year only.

Example#

ExampleMovies directed by Oscar winners (lecture)

SELECT title FROM movies
JOIN directors ON movies.movie_id = directors.movie_id
JOIN (
  SELECT DISTINCT person_id FROM awards
  WHERE award_name LIKE 'Academy Awards%'
) AS awardees ON directors.person_id = awardees.person_id;

The subquery yields a one-column table awardees(person_id) with the IDs of all Oscar winners. In terms of movies(movie_id, title, …), directors(movie_id, person_id) and awardees(person_id) the query is equivalent to

SELECT title FROM movies NATURAL JOIN directors NATURAL JOIN awardees;

the titles of all movies directed by Oscar winners.

ExampleBest movie per year (WS 23/24, Task 3.3)

SELECT year, title, score
FROM movies NATURAL JOIN (
  SELECT year, MAX(score) AS score FROM movies GROUP BY year
)
ORDER BY score DESC;

Subquery: (2021, 8.3), (2022, 8.2), (2023, 8.4). The natural join matches on year and score, so only each year’s best movie survives: (2023, Oppenheimer, 8.4), (2021, Spider-Man, 8.3), (2022, Top Gun, 8.2).

Sources#

Slides 05, p. 33 (definition, example), p. 34 (explanation), p. 29 (limits of GROUP BY)
DBIS_VL05 > Subqueries, DBIS_VL05 > Grouping
Exams: DBIS Exam WS2324 (not published) (Task 3.3), DBIS Exam WS2324-2 (not published) (Tasks 2.1, 3.3)

Connections

Mentioned in inline links6

group-by(T, cols, agg)
Missing: HAVING as a fourth argument $\varphi$ filtering result rows, WHERE vs. HAVING, the example group-by(movies, (2), ((3, max))) → (2023, 8.5), (2021, 8.0), (2022, 8.6), the hash-map implementation with cost $\sim n \cdot k$ , rows… §
SQL Query Structure
Wherever SQL expects a table, a whole SELECT query in parentheses may be written (Subquery). §
VL05 – Joins and more SQL
Subquery — a SELECT query used in place of a table §
SQL and Query Planning
Subquery — a SELECT query used in place of a table §
From a query to a result. A query SELECT … FROM … WHERE … is defined by a chain of operations on tables: the FROM tables are combined by the Cartesian Product, the WHERE expression selects rows ( $\sigma$ ), the SELECT list projects columns… §
SPARQL AggregationADIS
Sub-SELECT. A complete SELECT … WHERE { … } GROUP BY … in braces can stand inside a WHERE clause. Its result table is joined with the other patterns of the outer query via their shared variables, like a Subquery in SQL’s FROM. §
Advanced SPARQLADIS
| sub-SELECT | its result table, joined in | only its selected variables | subquery in FROM (Subquery) | §

Search in these mentions →