Subqueries & Derived Tables

Subqueries are queries inside other queries.
They help answer questions that require intermediate results.

1. Subqueries in Different Clauses

A. Subquery in the SELECT Clause

Used when you want a calculated value for each row.

Example: Total orders per customer shown alongside each customer.

SELECT c.customer_id, c.customer_name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS total_orders FROM customers c;

This produces one value per row.

B. Subquery in the WHERE Clause

Most common pattern: filtering using results from another query.

Example: Find customers whose total spend is above the overall average.

SELECT customer_id, total_spent FROM customer_totals WHERE total_spent > ( SELECT AVG(total_spent) FROM customer_totals ); 

C. Subquery in the FROM Clause (Derived Table / Inline View)

A subquery that acts like a temporary table.

Example: Compute totals first, then filter:

SELECT customer_id, total_spent FROM ( SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id ) AS t WHERE total_spent > 1000;

Use this when you need to reuse results or simplify complex logic.

2. Correlated vs Non-Correlated Subqueries

Non-Correlated Subquery

Does not depend on the outer query.
Runs once → its result is used by the outer query.

Example:

SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders); 

Correlated Subquery

Depends on outer query values.
Runs once per row of the outer query.

Example: Total orders for each customer.

SELECT c.customer_id, c.customer_name FROM customers c WHERE ( SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id ) > 5;

Think of it as:
“For each customer, run a query that counts their orders.”

3. Common Mistakes & Optimization Tips

Mistake 1 — Using subqueries when a JOIN is faster

Example:

SELECT * FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE status = 'active' ); 

Better:

SELECT o.* FROM orders o JOIN customers c USING (customer_id) WHERE c.status = 'active';

Mistake 2 — Using SELECT * inside subqueries

Subqueries in WHERE must return one column.

Mistake 3 — Expecting correlated subqueries to be efficient

Correlated subqueries run repeatedly.
Often better rewritten using a join or derived table.

Optimization Tips

• Use JOINs first, subqueries only when necessary.

• Use EXISTS instead of IN (SELECT...) for large datasets.

• Index columns used inside subqueries (FKs, filtering fields).

• Derived tables are useful when:

  • You aggregate first

  • Then filter (HAVING alternative)

4. Practical Exercises

Below are the two exercises you listed, with example solutions.

Exercise 1: Find users with above-average order value

Assume:

orders(order_id, user_id, order_total)

Solution using a subquery in WHERE

SELECT user_id, AVG(order_total) AS avg_order_value FROM orders GROUP BY user_id HAVING AVG(order_total) > ( SELECT AVG(order_total) FROM orders );

Exercise 2: Compare employee salaries against department averages

Assume:

employees(emp_id, name, dept_id, salary)

Employees whose salary is above their department's avg

SELECT e.emp_id, e.name, e.salary, e.dept_id FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id );

This is a correlated subquery — the inner query depends on e.dept_id.