Subqueries

Overview

A subquery is a SELECT statement nested inside another SQL statement, such as SELECT, INSERT, UPDATE, DELETE, or another subquery. It allows one query to use the result of another query as a source of values, a filter condition, or a derived table. Subqueries are part of the SQL standard since SQL-92 and are supported by all major relational database systems, though syntax and performance characteristics can vary. They are commonly used with comparison operators, IN, EXISTS, ANY, and ALL, and can appear in the SELECT list, FROM clause, or WHERE clause. A subquery that returns a single value is called a scalar subquery; one that returns multiple rows can be used with IN or EXISTS. Subqueries can be correlated or non-correlated. A non-correlated subquery executes independently of the outer query, while a correlated subquery references columns from the outer query and is evaluated per outer row. Understanding subqueries is essential for writing flexible queries that combine data from multiple tables or apply complex filtering logic without always resorting to joins or temporary tables.

Syntax and usage

Subqueries are enclosed in parentheses and can appear in several positions. In the WHERE clause, a scalar subquery can be compared directly: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);. For multiple values, use IN: SELECT * FROM departments WHERE id IN (SELECT department_id FROM employees WHERE salary > 100000);. EXISTS checks for the existence of rows: SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);. In the FROM clause, a subquery acts as a derived table and must have an alias: SELECT dept, avg_sal FROM (SELECT department_id AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS t;. In the SELECT list, a scalar subquery can compute a value per row: SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count FROM customers c;. Correlated subqueries reference outer columns and are often used with EXISTS or NOT EXISTS. Standard SQL also defines ANY and ALL for quantified comparisons, e.g., WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10). Vendor extensions exist, such as lateral joins or APPLY, but the core subquery syntax is widely portable.

How it works

Conceptually, a subquery is evaluated to produce a result set that the outer query then consumes. For non-correlated subqueries, the database can evaluate the subquery once and reuse its result. For correlated subqueries, the subquery may be evaluated repeatedly, once for each row processed by the outer query. The actual execution strategy depends on the optimizer. Many systems transform subqueries into joins or apply semi-join and anti-join operations for EXISTS and NOT EXISTS. Scalar subqueries must return at most one row and one column; if they return more, an error occurs. Subqueries in the FROM clause are materialized or merged into the outer query plan, depending on the optimizer. The presence of NULLs can affect results: NOT IN with a subquery that returns NULL yields unknown for all rows, often leading to empty results. Performance varies: correlated subqueries can be slower than equivalent joins if not optimized, but modern optimizers often rewrite them effectively. Understanding the difference between correlated and non-correlated subqueries helps in predicting behavior and writing efficient SQL.

Examples

1. Scalar subquery in WHERE: SELECT employee_id, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); returns employees earning above the company average.

2. IN subquery: SELECT customer_id, name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2024-01-01'); lists customers with recent orders.

3. EXISTS correlated subquery: SELECT department_id, department_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary > 100000); finds departments with at least one high earner.

4. Derived table in FROM: SELECT dept, avg_salary FROM (SELECT department_id AS dept, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_stats WHERE avg_salary > 50000; filters aggregated results.

5. Scalar subquery in SELECT list: SELECT product_id, product_name, (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.product_id) AS times_ordered FROM products p; adds a per-product order count.

6. ANY/ALL: SELECT employee_id FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10); returns employees earning more than every employee in department 10.

Common pitfalls

One frequent issue is using NOT IN with a subquery that can return NULL. If the subquery returns any NULL, the entire NOT IN condition evaluates to unknown for all rows, producing an empty result set. Use NOT EXISTS instead. Another pitfall is expecting a scalar subquery to return multiple rows; this raises an error at runtime. Correlated subqueries can cause performance problems if the optimizer does not rewrite them, especially when the subquery scans a large table for each outer row. Subqueries in the SELECT list can also be inefficient if they are executed per row without caching. When using subqueries in the FROM clause, forgetting an alias is a syntax error in standard SQL. Additionally, some databases restrict the use of subqueries in certain contexts, such as in CHECK constraints or as default values. Mixing correlated and non-correlated references incorrectly can lead to unexpected results. Finally, subqueries that return multiple columns cannot be used with scalar comparison operators; use row constructors or joins instead. Always test with representative data and check execution plans when performance matters.

Best practices

Prefer EXISTS or NOT EXISTS over IN or NOT IN when the subquery may return NULLs or when you only need to test for existence, as this avoids NULL-related surprises and often performs better. Use joins when the subquery is used solely for filtering and the join can express the same logic more efficiently. For derived tables, consider using common table expressions (CTEs) for better readability, especially when the subquery is complex or reused. Keep subqueries simple and avoid nesting too deeply; deep nesting harms readability and may confuse the optimizer. When using scalar subqueries in the SELECT list, ensure they return at most one row, and consider whether a join with aggregation would be more efficient. Always alias derived tables. Test queries with EXPLAIN or equivalent to understand how the subquery is executed. In correlated subqueries, ensure that the correlation condition is indexed appropriately. For vendor-specific features like lateral joins, use them only when necessary and document the portability implications. Finally, consider whether a subquery can be replaced by a window function or a join to improve performance and clarity.

Frequently asked questions

Q: What is the difference between a subquery and a join? A: A subquery nests a SELECT inside another statement, while a join combines columns from two or more tables in a single FROM clause. Joins are often more efficient for combining data, but subqueries can be clearer for existence checks or scalar computations. Many subqueries can be rewritten as joins, and vice versa.

Q: Can a subquery return multiple columns? A: Yes, but only in certain contexts. A subquery in the FROM clause can return multiple columns and acts as a derived table. A scalar subquery must return exactly one column and at most one row. Subqueries used with IN or EXISTS typically return one column, though EXISTS ignores the selected columns.

Q: Are subqueries always slower than joins? A: Not necessarily. Modern optimizers often transform subqueries into joins or semi-joins. Performance depends on the query, indexes, and database engine. Correlated subqueries may be slower if not optimized, but non-correlated subqueries can be very efficient. Always measure with realistic data.