Subqueries and Common Table Expressions (CTEs)

# CHAPTER 16

Subqueries and Common Table Expressions (CTEs) #

1. Introduction #

2. Learning Objectives #

• Write basic Subqueries in the WHERE clause.

• Understand the performance risks of Correlated Subqueries.

• Use the EXISTS operator for lightning-fast checks.

• Refactor messy Subqueries into clean WITH clauses (CTEs).

• Understand how CTEs improve query readability.

3. The Basic Subquery #

Let's solve the CEO's problem:

SELECT first_name, salary 
FROM employees 
WHERE salary > (
    -- The Subquery calculates the average first!
    SELECT AVG(salary) FROM employees
);

4. Subqueries with the IN Operator #

-- Find all users who have purchased a product in the 'Gaming' category
SELECT first_name, email 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM orders 
    JOIN products ON orders.product_id = products.id
    WHERE products.category = 'Gaming'
);

5. Correlated Subqueries (The Performance Killer) #

-- For every single employee, calculate the average salary of THEIR specific department
SELECT first_name, salary, department 
FROM employees outer_emp
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees inner_emp
    WHERE inner_emp.department = outer_emp.department -- The Correlation!
);

*(If you have 100,000 employees, this query executes the inner average calculation 100,000 times! Avoid these when possible).*

6. The EXISTS Operator #

-- Find users who have at least one order.
-- EXISTS is infinitely faster than IN for massive tables.
SELECT first_name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

7. Common Table Expressions (CTEs) #

-- Rewrite the CEO's query using a CTE!
WITH CompanyStats AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT first_name, salary 
FROM employees, CompanyStats -- We can select from the CTE!
WHERE salary > CompanyStats.avg_sal;

8. Mini Project: Complex Reporting with CTEs #

WITH DepartmentSales AS (
    -- Step 1: Calculate the gross sales for every department
    SELECT department_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY department_id
)
-- Step 2: Query our new virtual table and join the real names!
SELECT d.dept_name, ds.total_sales
FROM departments d
JOIN DepartmentSales ds ON d.dept_id = ds.department_id
WHERE ds.total_sales > 100000;

9. Common Mistakes #

• Returning Multiple Columns: A subquery used with an = or > operator MUST return exactly ONE column and ONE row. If your subquery returns (firstname, lastname), the outer query will crash because it cannot do math against two columns simultaneously.

• Overusing Subqueries instead of JOINs: Many beginners use subqueries to fetch related data. In almost all cases, an INNER JOIN is significantly faster and better optimized by the PostgreSQL engine.

10. Best Practices #

• Embrace CTEs: In modern data engineering, deeply nested subqueries are considered bad practice due to poor readability. If your query requires nesting, extract it into a WITH CTE block at the top of the file.

11. Exercises #

1. 1. Write a query using a subquery to find all products that have a price greater than the absolute MAX(price) found in the 'Toys' category.

1. 2. Explain why the EXISTS operator is faster than IN when dealing with massive datasets.

12. SQL Challenges #

SELECT * FROM users WHERE id IN (SELECT user_id FROM banned_accounts);

Solution:

WITH BannedUsers AS (
    SELECT user_id FROM banned_accounts
)
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM BannedUsers);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the performance differences between using IN versus EXISTS when filtering against a subquery that returns millions of rows.

• Q: What is a Recursive CTE in PostgreSQL, and in what specific data structure (e.g., an organizational chart) is it absolutely necessary?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #