Subqueries

Uncorrelated subqueries run independently of the outer query and return a single value or a set of values that do not reference columns from the outer query. They are often easier to optimize because the inner query can be executed once. A classic case is filtering by a constant derived from another query, such as retrieving products with prices above the maximum price found in a different category. Uncorrelated subqueries can be used in the WHERE, HAVING, or IN clauses. When used in IN, the inner query must return a single column, which the outer query compares against. In contrast, correlated subqueries reference outer query columns, making them dependent on the outer row. This dependence means the inner query is typically executed once per outer row, which can be expensive if not indexed properly. A common strategy is to rewrite a correlated subquery as a JOIN or use a CTE to improve readability and performance. As you gain comfort, practice identifying whether a subquery references outer columns and consider the performance implications of repeated execution.

EXISTS and NOT EXISTS are special operators that test for the presence of rows returned by a subquery. EXISTS returns true if the subquery yields at least one row; NOT EXISTS returns true if the subquery yields no rows. These operators are optimized by the database engine to stop scanning as soon as a match is found, which can be efficient for large datasets. A typical use case is identifying customers who have placed orders: SELECT customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); Here, the inner query looks for matching orders, and the outer query returns customers who have at least one order. NOT EXISTS, on the other hand, helps find records without a related entry, such as customers who never placed an order. Both forms support correlated inner queries and are powerful for anti-joins and verification checks without needing explicit joins.

Correlated subqueries reference the outer query’s columns, making the inner query depend on the current row. This means the inner query is evaluated for each row produced by the outer query. The classic example is calculating an attribute per group, such as listing departments with an average salary above a threshold defined by the outer query. While powerful, correlated subqueries can be less efficient because the inner query runs repeatedly. A common optimization is to rewrite the logic using a join to avoid repeating the inner computation for every row, or to use a CTE to materialize the inner result once and then reference it. Understanding when to use a correlated subquery versus a JOIN is a key skill in SQL performance tuning.

Scalar subqueries return a single value used in expressions. They are common in the SELECT clause to compute derived metrics per row or in the WHERE clause to compare a field to a computed value. A scalar subquery must return exactly one value; if it returns more than one, the query fails with an error unless used with an operator like IN. Practice with an example: find each employee’s salary as a percentage of the company’s total payroll. The inner subquery returns the total payroll, and the outer query calculates the percentage for each employee. Be mindful if the inner subquery can return NULL values, and consider how NULLs affect comparisons and arithmetic. Also, ensure that scalar subqueries are efficient by avoiding expensive repeated scans; use proper indexing or materialized results when possible.

Subqueries in the FROM clause create derived tables, which are temporary result sets you can query against in the outer query. This pattern can simplify complex logic by isolating an expensive calculation first, then reusing its results. Examples include computing per-category aggregates and joining that result to the main table. Derived tables must have valid column names; you can alias them for clarity. When using derived tables, ensure the inner query returns a single column or provide named columns for multiple columns. Performance considerations include materializing the derived table and indexing the outer join on the derived content. This technique is often interchangeable with common table expressions (CTEs) for readability and maintainability.