Subquery (Nested Query)

Level 4 — Querying & Data Retrieval (Intermediate SQL) A SELECT query nested inside another parent SQL query, evaluated dynamically to supply values or intermediate tables for parent processing.

1. Prerequisites

• SELECT — The baseline query command.
• WHERE Clause — The parent filter context where subqueries are commonly nested.

2. Term Category

SQL Command / Clause (Nested SQL Expressions): Subqueries are nested SELECT queries embedded within WHERE, FROM, or projection clauses of an outer query.

3. Explanation

Environment Context

• Universal Standard (Supported in all SQL databases. Evaluated by the query planner, which often optimizes subqueries into standard JOIN operations under the hood).

(1) Design Motivation — "Why did we design this?"

In SQL, you often need to filter records based on calculations that require checking the entire table:

• Find all products that cost more than the average product price.
• Find all users who registered on the most recent registration day.

If you try to write this using a simple query:

-- DANGER: This query crashes immediately!
SELECT name, price 
FROM products 
WHERE price > AVG(price); -- WRONG: Aggregates are not allowed in WHERE!

This crashes because WHERE runs before the average is calculated.

To solve this, you would have to run two separate queries in your backend application:

1. Run SELECT AVG(price) FROM products; -> returns $15.00.
2. Run SELECT name, price FROM products WHERE price > 15.00;.

This requires two network trips to the database.

We designed Subqueries to solve this.

You can nest the first query inside parentheses directly inside the second query. The database engine calculates the inner query first, feeds the output directly to the outer query, and returns the result in one single step.

(2) Placement Contexts

Subqueries can live in three primary locations inside a query:

1. In the WHERE clause (Scalar / List Filtering): Returns values to filter on (most common).
   • WHERE price > (SELECT AVG(price) FROM products)
2. In the FROM clause (Derived Tables): Acts as a temporary, on-the-fly table. Note: In Postgres, you must always assign an alias to a subquery inside a FROM clause!
   • FROM (SELECT * FROM log) AS temp_log
3. In the SELECT list (Correlated projection): Returns a single calculated value for every row in the output.

(3) Reality Metaphor

Imagine a math expression containing parentheses: x = 10 * (3 + 5)

You cannot multiply 10 until you know the value inside the parentheses.

You execute the inner expression first (3 + 5 = 8), substitute it back into the main equation (10 * 8), and calculate the final result (80).

A subquery is the SQL equivalent of the parenthesis expression.

(4) Code Examples

Subquery in WHERE

Find products cheaper than the average price:

CREATE TABLE product_catalog (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10,2)
);

-- Nested subquery calculates average price, outer query uses it to filter
SELECT name, price 
FROM product_catalog
WHERE price < (SELECT AVG(price) FROM product_catalog);

Subquery in FROM (Derived Table)

You must assign an alias to the subquery:

-- Treat the subquery results as an inline table named 'sub_table'
SELECT sub_table.name 
FROM (
  SELECT name, price FROM product_catalog WHERE price > 50.00
) AS sub_table;

4. Common Mistakes & Pitfalls

Mistake 1: Comparing a scalar operator (=, >, <) with a subquery returning multiple rows

The mistake: Writing a query that expects a single value, but the nested query returns a list:

-- BAD: This query crashes if multiple products cost exactly $10.00!
SELECT name FROM users 
WHERE balance = (SELECT price FROM product_catalog WHERE price = 10.00);
-- ERROR: more than one row returned by a subquery used as an expression

Why it's wrong: The equal operator = is a scalar operator; it expects exactly one number. If the subquery returns three rows, the equation balance = (10, 10, 10) is invalid, causing Postgres to abort.

Fix: If your subquery can return multiple rows, replace the scalar operator (=) with the set operator (IN).

/* Correct approach */
SELECT name FROM users 
WHERE balance IN (SELECT price FROM product_catalog WHERE price = 10.00);

Mistake 2: Using Subqueries in IN (...) Predicates When Subquery Returns NULL Values

The mistake: Writing WHERE id NOT IN (SELECT parent_id FROM t) when parent_id contains NULL values.

Why it's wrong: If a NOT IN (SELECT col ...) subquery returns even a single NULL value, NOT IN evaluates to NULL (Unknown) for ALL outer rows, returning ZERO rows! Use NOT EXISTS.

Incorrect:

SELECT * FROM users WHERE id NOT IN (SELECT manager_id FROM users); -- ❌ Fails if manager_id has NULL!

Fix:

SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM users m WHERE m.manager_id = u.id);

Mistake 3: Expecting Scalar Subqueries to Return Multiple Rows

The mistake: Writing SELECT name, (SELECT total FROM orders WHERE user_id = u.id) FROM users u;.

Why it's wrong: A scalar subquery in a SELECT projection list MUST return at most ONE row and ONE column. Returning multiple rows throws error more than one row returned by a subquery.

Incorrect:

SELECT name, (SELECT total FROM orders WHERE user_id = u.id) FROM users u; -- ❌ Subquery returns multiple rows!

Fix:

Use JOIN: SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

5. Practice Exercises

Exercise 1: Scalar Subqueries in WHERE Filtering

Scenario: Query products with price greater than the AVERAGE price of all products.

Requirements:

1. Execute WHERE price_cents > (SELECT AVG(price_cents) FROM products).

SELECT id, name, price_cents 
FROM products 
WHERE price_cents > (
  SELECT AVG(price_cents) 
  FROM products
);

Exercise 2: Correlated Subqueries in SELECT Projections

Scenario: Select customers alongside their latest order date using a correlated scalar subquery.

Requirements:

1. Select (SELECT MAX(created_at) FROM orders WHERE customer_id = customers.id).

SELECT 
  c.id, 
  c.company_name,
  (
    SELECT MAX(o.created_at) 
    FROM orders AS o 
    WHERE o.customer_id = c.id
  ) AS latest_order_date 
FROM customers AS c;

Exercise 3: Derived Table Subqueries in FROM Clauses

Scenario: Calculate average order count per customer by querying a derived table subquery in FROM.

Requirements:

1. Query FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS customer_orders.

SELECT 
  AVG(customer_orders.order_count) AS avg_orders_per_customer 
FROM (
  SELECT 
    customer_id, 
    COUNT(*) AS order_count 
  FROM orders 
  GROUP BY customer_id
) AS customer_orders;

6. Related Terms

• SELECT — The query starter.
• EXISTS / NOT EXISTS — Testing subquery row matching.

7. Key Takeaways

• A subquery is a SELECT statement nested inside another SQL parent query.
• Evaluated first (inner query) before the results are fed to the parent (outer query).
• Can be placed inside WHERE (filters), FROM (tables), or SELECT (projections).
• Subqueries in FROM clauses must always be assigned a custom alias.
• Scalar comparisons (=, >) crash if the subquery returns multiple rows; use IN.