SQL Subqueries & CTEs

SQL Subqueries & CTEs Tutorial: Advanced Querying Guide

📖 Introduction: Queries Inside Queries

So far, you've written queries that pull data directly from tables. But what if you need to answer questions like:

• "Who earns more than the company average?"
• "Which customers have never placed an order?"
• "Show me each employee's salary compared to their department average."

Subqueries let you embed one query inside another. CTEs (Common Table Expressions) let you break complex queries into readable, reusable blocks.

Analogy: A subquery is like asking a friend a question, then using their answer to ask someone else a follow-up question. A CTE is like writing down the answer on a sticky note so you can reference it multiple times.

🛠️ Setting Up Practice Data

sqlCopy
1CREATE DATABASE company_db;
2USE company_db;
3
4CREATE TABLE employees (
5    emp_id INT PRIMARY KEY AUTO_INCREMENT,
6    emp_name VARCHAR(100),
7    department VARCHAR(50),
8    salary DECIMAL(10,2),
9    manager_id INT
10);
11
12CREATE TABLE customers (
13    customer_id INT PRIMARY KEY AUTO_INCREMENT,
14    customer_name VARCHAR(100)
15);
16
17CREATE TABLE orders (
18    order_id INT PRIMARY KEY AUTO_INCREMENT,
19    customer_id INT,
20    amount DECIMAL(10,2),
21    order_date DATE
22);
23
24-- Insert employees
25INSERT INTO employees (emp_name, department, salary, manager_id) VALUES
26('Alice Johnson', 'IT', 90000, NULL),
27('Bob Smith', 'HR', 55000, 1),
28('Carol White', 'IT', 80000, 1),
29('David Brown', 'Sales', 75000, 1),
30('Eve Davis', 'HR', 60000, 2),
31('Frank Miller', 'IT', 85000, 3),
32('Grace Lee', 'Sales', 72000, 4),
33('Henry Wilson', 'Marketing', 65000, 1);
34
35-- Insert customers
36INSERT INTO customers (customer_name) VALUES
37('Acme Corp'), ('Globex'), ('Initech'), ('Umbrella');
38
39-- Insert orders
40INSERT INTO orders (customer_id, amount, order_date) VALUES
41(1, 1200.00, '2026-08-01'),
42(1, 800.00, '2026-08-05'),
43(2, 2500.00, '2026-08-02'),
44(1, 300.00, '2026-08-10'),
45(3, 1500.00, '2026-08-03');

1️⃣ Subqueries in WHERE — Filtering with Inner Results

Purpose: Use the result of one query to filter another query.

Example 1: Single-Row Subquery (Returns One Value)

Question: Who earns more than the company average salary?

sqlCopy
1SELECT emp_name, salary
2FROM employees
3WHERE salary > (SELECT AVG(salary) FROM employees);

How it works:

1. Inner query runs first: SELECT AVG(salary) FROM employees → 78500
2. Outer query runs: SELECT ... WHERE salary > 78500

Output:

+---------------+----------+
| emp_name      | salary   |
+---------------+----------+
| Alice Johnson | 90000.00 |
| Frank Miller  | 85000.00 |
+---------------+----------+

Single-row subquery returns exactly one value. Use with =, >, <, >=, <=, <>.

Example 2: Multi-Row Subquery (Returns Multiple Values)

Question: Which customers have placed at least one order?

sqlCopy
1SELECT customer_name
2FROM customers
3WHERE customer_id IN (SELECT customer_id FROM orders);

How it works:

1. Inner query: SELECT customer_id FROM orders → 1, 2, 3
2. Outer query checks: WHERE customer_id IN (1, 2, 3)

Output:

+---------------+
| customer_name |
+---------------+
| Acme Corp     |
| Globex        |
| Initech       |
+---------------+

Multi-row subquery returns multiple values. Use with IN, NOT IN, ANY, ALL.

Example 3: NOT IN — Find Missing Data

Question: Which customers have never placed an order?

sqlCopy
1SELECT customer_name
2FROM customers
3WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Output:

+---------------+
| customer_name |
+---------------+
| Umbrella      |
+---------------+

2️⃣ Subqueries in FROM — Treat Results as a Table

Purpose: Use a query result as if it were a temporary table.

Example 4: Department Averages as a Table

Question: Show each department and its average salary.

sqlCopy
1SELECT dept_avg.department, dept_avg.avg_sal
2FROM (
3    SELECT department, AVG(salary) AS avg_sal
4    FROM employees
5    GROUP BY department
6) AS dept_avg;

How it works:

1. Inner query creates a temporary result set (like a mini-table)
2. Outer query selects from that result set

Output:

+------------+-------------+
| department | avg_sal     |
+------------+-------------+
| HR         | 57500.000000|
| IT         | 85000.000000|
| Marketing  | 65000.000000|
| Sales      | 73500.000000|
+------------+-------------+

Must use an alias (AS dept_avg) when using a subquery in FROM.

Example 5: Filter Department Averages

Question: Which departments have an average salary above $70,000?

sqlCopy
1SELECT department, avg_sal
2FROM (
3    SELECT department, AVG(salary) AS avg_sal
4    FROM employees
5    GROUP BY department
6) AS dept_avg
7WHERE avg_sal > 70000;

3️⃣ Subqueries in SELECT — Scalar Subqueries

Purpose: Add a calculated column from another table for each row.

Example 6: Order Count per Customer

Question: Show each customer and how many orders they've placed.

sqlCopy
1SELECT 
2    c.customer_name,
3    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
4FROM customers c;

How it works:

• For each customer row, the subquery counts their orders
• This is called a correlated subquery because it references c.customer_id from the outer query

Output:

+---------------+-------------+
| customer_name | order_count |
+---------------+-------------+
| Acme Corp     | 3           |
| Globex        | 1           |
| Initech       | 1           |
| Umbrella      | 0           |
+---------------+-------------+

Performance Note: Correlated subqueries run once per outer row. For large tables, JOINs are usually faster.

4️⃣ Correlated Subqueries — Row-by-Row Comparison

Purpose: The inner query depends on the outer query's current row.

Example 7: Employees Earning Above Department Average

Question: Who earns more than their own department's average?

sqlCopy
1SELECT e1.emp_name, e1.department, e1.salary
2FROM employees e1
3WHERE e1.salary > (
4    SELECT AVG(e2.salary)
5    FROM employees e2
6    WHERE e2.department = e1.department
7);

How it works:

1. Outer query picks Alice (IT, $90,000)
2. Inner query calculates IT average: (90000 + 80000 + 85000) / 3 = 85000
3. Compare: 90000 > 85000 → Yes, include Alice
4. Repeat for every employee

Output:

+---------------+------------+----------+
| emp_name      | department | salary   |
+---------------+------------+----------+
| Alice Johnson | IT         | 90000.00 |
| Frank Miller  | IT         | 85000.00 |
+---------------+------------+----------+

5️⃣ CTEs with WITH Clause — Readable, Reusable Queries

Purpose: Define a temporary named result set that you can reference multiple times in the main query.

Syntax:

sqlCopy
1WITH cte_name AS (
2    SELECT ...
3)
4SELECT * FROM cte_name;

Example 8: Simple CTE for High Earners

sqlCopy
1WITH high_earners AS (
2    SELECT * FROM employees WHERE salary > 80000
3)
4SELECT * FROM high_earners WHERE department = 'IT';

Output:

+--------+---------------+------------+----------+------------+
| emp_id | emp_name      | department | salary   | manager_id |
+--------+---------------+------------+----------+------------+
| 1      | Alice Johnson | IT         | 90000.00 | NULL       |
| 6      | Frank Miller  | IT         | 85000.00 | 3          |
+--------+---------------+------------+----------+------------+

Why use CTEs?

• More readable than nested subqueries
• Can be referenced multiple times in the main query
• Easier to debug (you can test the CTE independently)

Example 9: Multiple CTEs

sqlCopy
1WITH 
2dept_avg AS (
3    SELECT department, AVG(salary) AS avg_salary
4    FROM employees
5    GROUP BY department
6),
7high_earners AS (
8    SELECT * FROM employees WHERE salary > 80000
9)
10SELECT h.emp_name, h.salary, d.avg_salary
11FROM high_earners h
12JOIN dept_avg d ON h.department = d.department;

6️⃣ Recursive CTEs — Hierarchical Data

Purpose: Query tree-like or hierarchical data (org charts, categories, folders).

Syntax:

sqlCopy
1WITH RECURSIVE cte_name AS (
2    -- Anchor member: starting point
3    SELECT ...
4    UNION ALL
5    -- Recursive member: references the CTE itself
6    SELECT ...
7)
8SELECT * FROM cte_name;

Example 10: Organizational Hierarchy

Question: Show the management hierarchy with levels.

sqlCopy
1WITH RECURSIVE hierarchy AS (
2    -- Anchor: Start with the CEO (no manager)
3    SELECT emp_id, emp_name, manager_id, 0 AS level
4    FROM employees
5    WHERE manager_id IS NULL
6    
7    UNION ALL
8    
9    -- Recursive: Find employees who report to someone in the hierarchy
10    SELECT e.emp_id, e.emp_name, e.manager_id, h.level + 1
11    FROM employees e
12    JOIN hierarchy h ON e.manager_id = h.emp_id
13)
14SELECT 
15    REPEAT('  ', level) || emp_name AS org_chart,
16    level,
17    emp_id,
18    manager_id
19FROM hierarchy
20ORDER BY level, emp_name;

How it works:

1. Anchor: Finds Alice (CEO, level 0)
2. Round 1: Finds everyone who reports to Alice → Bob, Carol, David, Henry (level 1)
3. Round 2: Finds everyone who reports to Bob, Carol, David, Henry → Eve, Frank, Grace (level 2)
4. Stops: When no more matches are found

Output:

+------------------------+-------+--------+------------+
| org_chart              | level | emp_id | manager_id |
+------------------------+-------+--------+------------+
| Alice Johnson          | 0     | 1      | NULL       |
|   Bob Smith            | 1     | 2      | 1          |
|   Carol White          | 1     | 3      | 1          |
|   David Brown          | 1     | 4      | 1          |
|   Henry Wilson         | 1     | 8      | 1          |
|     Eve Davis          | 2     | 5      | 2          |
|     Frank Miller       | 2     | 6      | 3          |
|     Grace Lee          | 2     | 7      | 4          |
+------------------------+-------+--------+------------+

Note: REPEAT(' ', level) adds indentation. In MySQL, use REPEAT(' ', level) or CONCAT().

📝 Complete Subquery & CTE Reference

🚀 Hands-On Project: Org Chart & Sales Ranking

Project 1: Organizational Hierarchy Report

sqlCopy
1WITH RECURSIVE org_hierarchy AS (
2    SELECT 
3        emp_id,
4        emp_name,
5        manager_id,
6        CAST(emp_name AS CHAR(500)) AS path,
7        0 AS depth
8    FROM employees
9    WHERE manager_id IS NULL
10    
11    UNION ALL
12    
13    SELECT 
14        e.emp_id,
15        e.emp_name,
16        e.manager_id,
17        CONCAT(oh.path, ' > ', e.emp_name),
18        oh.depth + 1
19    FROM employees e
20    JOIN org_hierarchy oh ON e.manager_id = oh.emp_id
21)
22SELECT 
23    REPEAT('|-- ', depth) || emp_name AS hierarchy,
24    path AS full_path,
25    depth
26FROM org_hierarchy
27ORDER BY path;

Project 2: Sales Ranking System

sqlCopy
1WITH customer_stats AS (
2    SELECT 
3        c.customer_id,
4        c.customer_name,
5        COUNT(o.order_id) AS total_orders,
6        COALESCE(SUM(o.amount), 0) AS total_spent,
7        COALESCE(AVG(o.amount), 0) AS avg_order
8    FROM customers c
9    LEFT JOIN orders o ON c.customer_id = o.customer_id
10    GROUP BY c.customer_id, c.customer_name
11),
12ranked_customers AS (
13    SELECT 
14        customer_name,
15        total_orders,
16        total_spent,
17        avg_order,
18        RANK() OVER (ORDER BY total_spent DESC) AS spend_rank,
19        CASE 
20            WHEN total_spent > 2000 THEN 'Platinum'
21            WHEN total_spent > 1000 THEN 'Gold'
22            WHEN total_spent > 0 THEN 'Silver'
23            ELSE 'Bronze'
24        END AS customer_tier
25    FROM customer_stats
26)
27SELECT * FROM ranked_customers ORDER BY spend_rank;

Output:

+---------------+--------------+-------------+-----------+------------+---------------+
| customer_name | total_orders | total_spent | avg_order | spend_rank | customer_tier |
+---------------+--------------+-------------+-----------+------------+---------------+
| Acme Corp     | 3            | 2300.00     | 766.67    | 1          | Platinum      |
| Globex        | 1            | 2500.00     | 2500.00   | 2          | Platinum      |
| Initech       | 1            | 1500.00     | 1500.00   | 3          | Gold          |
| Umbrella      | 0            | 0.00        | 0.00      | 4          | Bronze        |
+---------------+--------------+-------------+-----------+------------+---------------+

✅ Module 8 Summary

🎯 Practice Exercises

1. Find employees whose salary is above the company average using a subquery.
2. Find customers who have placed more orders than the average customer.
3. Use a subquery in FROM to show departments with average salary above $70,000.
4. Rewrite Exercise 1 using a CTE instead of a subquery.
5. Create a recursive CTE to show the full management chain for employee 'Grace Lee'.
6. Write a query that uses both a CTE and a subquery together.
7. Find the top 3 customers by total spend using a CTE with RANK().

🎓 What's Next?

In Module 9, you'll learn Constraints, Indexes & Keys — how to enforce data integrity and speed up your queries with PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and database indexes! 🚀