liveBook · Manning

Scalar subquery

As explained in DuckDB in Action

A scalar uncorrelated subquery is an uncorrelated subquery that returns one scalar value.

A scalar uncorrelated subquery is nested SQL that returns exactly one value without depending on the outer query. Used as a comparison expression, it lets a query filter rows against an aggregate or other single result. section 4.3 section 4.3.1

Find readings at maximum power

SELECT read_on, power FROM readings WHERE power = (SELECT max(power) FROM readings); The inner query produces the maximum power, and the outer query returns every reading matching it. The example returns five readings at 133,900 W.

Equality and inequality operators require a scalar result. For multirow results, use set comparisons such as IN, EXISTS, ANY, or ALL; use a correlated subquery when the inner value must vary by outer row. section 4.3.1 section 4.3

How DuckDB in Action covers it

How deep it goes
Explains definition, cardinality, comparison use, and correlated variation, but defers broader set-comparison behavior to related operators.
Examples
Demonstrates maximum-power filtering and reports five matching readings at 133,900 W.
What it assumes
Assumes basic SQL queries, WHERE comparisons, aggregation, and awareness that nested queries can return one or many rows.
What you can do after
Apply it via SQL to filter rows against an aggregate value and choose set comparisons or correlation for different result shapes.
How it connects
Connects scalar subqueries with uncorrelated subqueries, correlated subqueries, aggregate functions, JOIN, IN, EXISTS, ANY, and ALL.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.