Subqueries

---

Subqueries (Nested Queries)

Subqueries—also called nested queries—allow you to embed one query inside another. While they are expressive and widely supported, they tend to be harder to read, reuse, and optimize compared to CTEs. In modern engines (PostgreSQL, SQL Server, Snowflake, Databricks, BigQuery, Oracle), CTEs are almost always preferred from a readability and maintainability standpoint.

However, subqueries remain important, especially for inline filtering, simple tests of existence, correlated queries, and when targeting older engines with limited CTE support.

---

Why Subqueries Are Often Worse Than CTEs

Subqueries are not “terrible,” but they are inferior in several practical ways:

✅ CTEs Improve Readability and Maintainability

• Subqueries nest logic deeply → harder to understand.

• CTEs separate logic → readable step-by-step.

✅ CTEs Allow Reuse

A subquery can’t be referenced twice; a CTE can.

✅ CTEs Enable Greedy Optimization (in modern engines)

• PostgreSQL (since v12), SQL Server, Oracle, BigQuery, Snowflake optimize CTEs similarly to subqueries.

• Subqueries may be re-evaluated if the optimizer cannot guarantee reuse.

⚠️ Subqueries Can Have Performance Pitfalls

• NOT IN with NULL produces wrong results unless carefully guarded.

• Many engines struggle to optimize deeply nested subqueries.

• Harder for the optimizer to “push down” predicates.

🎯 Best practice

Use subqueries only when they are the simplest expression of intent; otherwise use CTEs.

---

Types of Subqueries + Examples

1. IN Subqueries

These return a set of values for filtering.

Find customers who have placed orders

SELECT customer_id, customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
);

Find products in specific categories

SELECT product_name, price
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories 
    WHERE category_name IN ('Electronics', 'Books', 'Clothing')
);

---

2. NOT IN Subqueries (Be Careful!)

NOT IN ignores rows if the subquery returns NULL → often broken.

Find customers who never placed an order

SELECT customer_id, customer_name, email
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL   -- Prevent NULL poisoning
);

---

3. Comparison Subqueries (ALL / ANY)

Employees who earn more than ANYONE in Marketing

SELECT employee_name, salary, department
FROM employees
WHERE salary > ALL (
    SELECT salary 
    FROM employees 
    WHERE department = 'Marketing'
);

Alternative using MAX()

SELECT employee_name, salary, department
FROM employees
WHERE salary > (
    SELECT MAX(salary)
    FROM employees
    WHERE department = 'Marketing'
);

---

4. EXISTS / NOT EXISTS (Most Efficient Filter Type)

EXISTS returns true as soon as one row matches—very efficient.

Basic EXISTS pattern

SELECT columns
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.foreign_key = table1.primary_key
);

Find employees with no direct reports

SELECT employee_name, title
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees subordinate
    WHERE subordinate.manager_id = e.employee_id
);

---

5. Named Subqueries (Inline Views / Derived Tables)

You can name a subquery using AS to treat it like a temporary inline table.

Example

SELECT student.sid, name
FROM student
JOIN (
    SELECT MAX(sid) AS sid
    FROM enrolled
) AS max_e
ON student.sid = max_e.sid;

Another example using aggregation:

SELECT c.first_name, c.last_name,
       pymnt.num_rentals, pymnt.tot_payments
FROM customer c
JOIN (
    SELECT customer_id, COUNT(*) AS num_rentals, SUM(amount) AS tot_payments
    FROM payment
    GROUP BY customer_id
) AS pymnt
ON c.customer_id = pymnt.customer_id;

---

6. Nested Query Result Operators

Operator	Meaning
ALL	Expression must be true for every value in the subquery
ANY	Expression must be true for at least one value
IN	Equivalent to = ANY(subquery)
EXISTS	True if the subquery returns at least one row

---

7. OLAP / Analytics Subquery Patterns

Regions that performed above average

SELECT region_name, total_sales
FROM (
    SELECT region_id, SUM(order_amount) AS total_sales
    FROM orders o 
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY region_id
) AS regional_sales
JOIN regions r ON regional_sales.region_id = r.region_id
WHERE regional_sales.total_sales > (
    SELECT AVG(region_total)
    FROM (
        SELECT SUM(order_amount) AS region_total
        FROM orders o2 
        JOIN customers c2 ON o2.customer_id = c2.customer_id
        GROUP BY c2.region_id
    ) AS avg_calc
);

---

Above-average regions using EXISTS

SELECT region_name
FROM regions r
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT c.region_id, SUM(o.total_amount) AS region_sales
        FROM orders o
        JOIN customers c ON o.customer_id = c.customer_id
        WHERE o.order_date >= '2024-01-01'
        GROUP BY c.region_id
        HAVING SUM(o.total_amount) >
            (SELECT AVG(total_sales)
             FROM (
                 SELECT SUM(total_amount) AS total_sales
                 FROM orders
                 WHERE order_date >= '2024-01-01'
             ) AS avg_calc)
    ) AS high_performing
    WHERE high_performing.region_id = r.region_id
);

---