SQL Subqueries: Patterns for Nested Queries That Stay Readable
Subqueries can be elegant or a tangled mess. After writing thousands of them, these are the patterns that keep nested queries clear. Subqueries are queries inside queries. They let you express problems that require intermediate results without writing multiple statements or temporary tables. I use them daily, but they have a reputation for being hard to read because nesting can get deep quickly. After writing thousands of subqueries, I have patterns that keep them clear and the situations where each type fits best. Scalar Subqueries in the SELECT List A scalar subquery returns a single row and a single column. You can place it anywhere a single value is expected, including the SELECT list, WHERE clause, and HAVING clause. I use it to attach a related value to each row without a join. SELECT product_name, price, (SELECT AVG(price) FROM products) AS market_avg, price - (SELECT AVG(price) FROM products) AS diff_from_avg FROM products; This adds the overall average price to every product row. A join would not work here because the average is a single value, not a per-row match. Scalar subqueries express this directly. The database rewrites many of them into joins…