A subquery is a nested SELECT query enclosed in parentheses and embedded within a larger parent SQL statement.
Subqueries can return a single scalar value, a single column list of values, or a multi-column derived table.
A scalar subquery returns exactly one row with one column, allowing it to be used anywhere a constant expression is valid.
For example, comparing individual product prices against the overall average price uses a scalar subquery in the WHERE clause.
If a scalar subquery unexpectedly returns multiple rows at runtime, the database engine aborts execution with an error.
Column subqueries return a single column with multiple rows, commonly evaluated using IN, ANY, or ALL operators.
The IN operator tests whether an outer column value exists within the list of values returned by the nested subquery.
Table subqueries appear in the FROM clause, creating a derived table that the outer query joins and filters like a real table.
In standard SQL, derived tables in the FROM clause must be assigned an explicit table alias to be referenced properly.
An independent subquery executes once, passing its static result set to the outer query for final processing.
In contrast, a correlated subquery references columns from the outer query, re-executing for every row evaluated by the outer query.
Correlated subqueries are often evaluated using the EXISTS operator to test whether matching records exist in a related table.
Because correlated subqueries can execute repeatedly, they can cause significant performance bottlenecks on large tables.
Database query optimisers frequently unnest subqueries into equivalent joins to take advantage of index lookups and hash joins.
When subqueries become deeply nested and difficult to read, analysts should refactor them into common table expressions.