SqlDatabaseIntermediate

Subquery (nested SELECT)

Uses a SELECT inside another query to compute or filter results.

Review the syntaxStudy the examplesOpen the coding app
SELECT col, (SELECT ...) AS derived FROM table;
SELECT * FROM t WHERE col IN (SELECT ...);
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Uses a SELECT inside another query to compute or filter results.

A subquery is a nested SELECT statement used to provide data to an outer query. Subqueries are categorized by their return structure: scalar (single value), row (single row, multiple columns), or table (multiple rows and columns). While useful for data isolation, subqueries—especially correlated subqueries that reference outer query columns—can introduce performance bottlenecks as they may execute once for every row in the outer result set. Modern optimizers often rewrite subqueries as JOINs, but manual conversion is sometimes necessary for complex predicates. An important edge case involves NULL handling; for instance, the NOT IN operator will return zero results if the subquery result set contains even a single NULL value due to SQL three-valued logic.

Quick reference

Syntax

SELECT col, (SELECT ...) AS derived FROM table;
SELECT * FROM t WHERE col IN (SELECT ...);

Inputs

Parameters

inner SELECTA query that returns a value or set used by the outer query.

See it in practice

Examples

1

Scalar Subquery in SELECT

SELECT name, price, (SELECT AVG(price) FROM products) AS avg_price FROM products;
Output:
| name | price | avg_price | |------|-------|-----------| | Desk | 150.0 | 125.0 | | Lamp | 100.0 | 125.0 |

Calculates the global average price and displays it alongside every individual product row.

2

Filtering with a Subquery (IN)

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
Output:
| name | |-------| | Alice | | Bob |

Filters employees based on a list of department IDs generated by a separate query.

3

Derived Table in FROM Clause

SELECT monthly_stats.month, monthly_stats.total_sales FROM (SELECT month, SUM(amount) AS total_sales FROM sales GROUP BY month) AS monthly_stats WHERE monthly_stats.total_sales > 1000;
Output:
| month | total_sales | |-------|-------------| | Jan | 1500.0 | | Mar | 1200.0 |

Treats the result of a nested SELECT as a temporary table (aliased as tats) for further filtering.

Debug faster

Common Errors

1

CardinalityError

Cause: A scalar subquery (used in SELECT or with comparison operators) returns more than one row.

Fix: Use LIMIT 1, an aggregate function like MAX(), or switch to the IN operator.

SELECT name, (SELECT order_id FROM orders) FROM users; -- Fails if user has >1 order
2

LogicError

Cause: Using NOT IN with a subquery that contains NULL values.

Fix: Add a WHERE column IS NOT NULL clause to the subquery or use NOT EXISTS.

SELECT * FROM t1 WHERE id NOT IN (SELECT nullable_id FROM t2);

Runtime support

Compatibility

All SQL databasesSQL-92 (Standard)

Optimizer behavior varies by DB

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Uses a SELECT inside another query to compute or filter results.

inner SELECT: A query that returns a value or set used by the outer query.

CardinalityError: Use LIMIT 1, an aggregate function like MAX(), or switch to the IN operator. LogicError: Add a WHERE column IS NOT NULL clause to the subquery or use NOT EXISTS.