SQL Subqueries | Nested SELECT Statements

# CHAPTER 18

Subqueries and Nested Queries #

1. Introduction #

2. Learning Objectives #

• Understand the structural syntax of a Subquery.

• Use a Subquery inside a WHERE clause for dynamic filtering.

• Use a Subquery inside a SELECT clause to generate calculated columns.

• Understand the EXISTS operator for performance optimization.

• Differentiate between a Subquery and a JOIN.

3. The Subquery in the WHERE Clause #

SELECT first_name, salary 
FROM employees 
WHERE salary > (
    -- This inner Subquery executes FIRST.
    -- It returns a single number (e.g., 65000).
    -- The outer query then becomes: WHERE salary > 65000
    SELECT AVG(salary) FROM employees
);

*(The inner query must be wrapped in parentheses ()).*

4. Subqueries returning a List (IN) #

-- Find all customers who have placed an order in the last 30 days
SELECT customer_name FROM customers 
WHERE id IN (
    -- This subquery returns a massive list of IDs (e.g., 5, 12, 99)
    SELECT DISTINCT customer_id FROM orders 
    WHERE order_date > '2023-10-01'
);

*(Note: This exact same result could be achieved with a JOIN. Developers often debate which is better. Generally, JOIN is faster, but Subqueries are sometimes easier for humans to read).*

5. The Subquery in the SELECT Clause #

SELECT 
    product_name, 
    price,
    (SELECT AVG(price) FROM products) AS 'Global Average',
    price - (SELECT AVG(price) FROM products) AS 'Difference From Avg'
FROM products;

*(Result: A beautiful grid comparing every single product's price to the global average!)*

6. The EXISTS Operator (High Performance) #

-- Find customers who have at least one active order
SELECT customer_name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
    AND o.status = 'Active'
);

*(This is a Correlated Subquery. The inner query relies on the c.id from the outer query to function!).*

7. Mini Project: The HR Analytics Dashboard #

-- 1. Who is our highest-paid employee?
SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 2. Which departments have NO employees assigned?
SELECT dept_name FROM departments
WHERE id NOT IN (SELECT DISTINCT dept_id FROM employees);

8. Common Mistakes #

• Returning Multiple Columns: If you write WHERE salary = (SELECT id, salary FROM employees), the query will crash. A subquery used with a standard mathematical operator (=, >, <) can ONLY return a single column and a single row (a scalar value).

• Subquery Performance: Writing a Correlated Subquery inside a SELECT statement forces the inner query to re-execute for *every single row* in the outer table. If you have 1 million rows, the inner query runs 1 million times. This is called the "N+1 Problem" and destroys server performance.

9. Best Practices #

• JOIN vs Subquery: As a general rule of database architecture, if you can write a query using a standard JOIN, you should. The SQL Query Optimizer is incredibly efficient at processing JOINs. Use Subqueries specifically when calculating aggregates (MAX, AVG) or utilizing the EXISTS operator.

10. Exercises #

1. 1. What punctuation marks must encapsulate a nested Subquery?

1. 2. If a Subquery in a WHERE clause returns a list of 50 different IDs, what operator must you use instead of the equals (=) sign?

11. SQL Challenges #

SELECT title FROM movies
WHERE budget > (SELECT MAX(budget) FROM movies WHERE release_year = 2010);

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Differentiate between a standard Subquery and a "Correlated Subquery". Provide an example of how a Correlated Subquery operates row-by-row against the outer query.

• Q: You are tasked with finding all Users who have never placed an Order. Explain how you could solve this using a LEFT JOIN combined with a WHERE NULL clause, versus solving it using a NOT EXISTS subquery.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #