TECH **SQL Subqueries for Data Analysis: A Zero-Fluff, Hands-On Guide**

SQL Subqueries for Data Analysis: A Zero-Fluff, Hands-On Guide

(Correlated vs. Non-Correlated in SELECT, FROM, and WHERE)

1. What This Is & Why It Matters

Subqueries are SQL’s Swiss Army knife—they let you nest one query inside another to break down complex problems into smaller, manageable pieces. In data analysis, you’ll use them daily to: - Filter data dynamically (e.g., "Show me all customers who spent more than the average order value").
- Join tables without explicit JOIN syntax (e.g., "Get product details for items in a specific category").
- Calculate aggregates on the fly (e.g., "Compare each employee’s salary to their department’s average").

Why this matters in production:
- Legacy systems often rely on subqueries because they’re more readable than complex joins (or because the original dev didn’t know better).
- Performance pitfalls lurk here: A poorly written subquery can turn a 1-second query into a 10-minute disaster.
- Certifications (PL-300, Google Data Analytics, etc.) test this heavily—you’ll see questions like: "Which subquery type is most efficient for filtering rows based on a dynamic condition?"

Real-world scenario:
You’re analyzing e-commerce data. Your boss asks: "Show me all orders where the customer’s lifetime value (LTV) is in the top 10% of all customers." You can’t answer this with a simple WHERE clause—you need a subquery to calculate the 90th percentile LTV first.

2. Core Concepts & Components

1. Non-Correlated Subquery

• Definition: A subquery that executes once and returns a single value (or set of values) to the outer query.
• Production insight: These are fast because the database runs them once and caches the result. Use them for static filters (e.g., "Get all orders from 2023").
• Example:
  sql SELECT customer_id, order_date FROM orders WHERE order_date > (SELECT MAX(order_date) FROM orders WHERE YEAR(order_date) = 2022);

2. Correlated Subquery

• Definition: A subquery that references columns from the outer query and executes once per row in the outer query.
• Production insight: These are slow—they act like a loop. Use them only when you need row-by-row comparisons (e.g., "Find employees earning more than their department’s average").
• Example:
  sql SELECT e.name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

3. Subquery in SELECT

• Definition: A subquery used as a column in the SELECT clause.
• Production insight: Useful for calculating metrics on the fly (e.g., "Show each product’s price alongside the category average").
• Example:
  sql SELECT
product_name,
price,
(SELECT AVG(price) FROM products WHERE category_id = p.category_id) AS avg_category_price FROM products p;

4. Subquery in FROM

• Definition: A subquery that acts as a temporary table in the FROM clause.
• Production insight: Great for pre-aggregating data before joining (e.g., "Get monthly sales totals, then join with customer data").
• Example:
  sql SELECT c.customer_name, s.monthly_sales FROM customers c JOIN (
SELECT customer_id, SUM(amount) AS monthly_sales
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY customer_id ) s ON c.customer_id = s.customer_id;

5. Subquery in WHERE

• Definition: A subquery used to filter rows in the outer query.
• Production insight: The most common use case. Non-correlated subqueries here are efficient; correlated ones can kill performance.
• Example:
  sql SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM order_items WHERE quantity > 10);

6. EXISTS vs. IN

• EXISTS:
• Checks if a subquery returns any rows (stops at the first match).
• Production insight: Faster than IN for large datasets because it doesn’t need to fetch all values.

• Example:
  sql
SELECT customer_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

• IN:

• Checks if a value matches any value in a list (fetches all subquery results first).
• Production insight: Slower than EXISTS for large datasets. Avoid with NULL values (they’ll break your query).
• Example:
  sql
SELECT product_name
FROM products
WHERE category_id IN (1, 2, 3);

7. ANY / SOME / ALL

• ANY / SOME:
• Returns TRUE if a value matches any value in the subquery.
• Production insight: Useful for range comparisons (e.g., "Find products priced higher than any product in category X").

• Example:
  sql
SELECT product_name
FROM products
WHERE price > ANY (SELECT price FROM products WHERE category_id = 5);

• ALL:

• Returns TRUE if a value matches all values in the subquery.
• Production insight: Rarely used, but powerful for edge cases (e.g., "Find customers who ordered every product in a category").
• Example:
  sql
SELECT customer_name
FROM customers
WHERE customer_id = ALL (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

3. Step-by-Step Hands-On: Solving a Real-World Problem

Prerequisites

• A SQL environment (e.g., PostgreSQL, MySQL, SQL Server, or BigQuery).
• A sample database with tables:
• customers (customer_id, name, email)
• orders (order_id, customer_id, order_date, amount)
• order_items (order_item_id, order_id, product_id, quantity, price)

Task:

"Show me all customers who placed orders in 2023, along with their total spend and how it compares to the average customer spend in their state."

Step 1: Write a non-correlated subquery to calculate the average spend per state.

SELECT state, AVG(total_spend) AS avg_state_spend
FROM (
  SELECT c.state, c.customer_id, SUM(o.amount) AS total_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
  GROUP BY c.state, c.customer_id
) customer_spend
GROUP BY state;

Expected output:
| state | avg_state_spend | |-------|-----------------| | CA | 1250.50 | | NY | 980.75 | | TX | 1100.00 |

Step 2: Use a correlated subquery to compare each customer’s spend to their state’s average.

SELECT
  c.customer_id,
  c.name,
  c.state,
  SUM(o.amount) AS total_spend,
  (
    SELECT AVG(total_spend)
    FROM (
      SELECT SUM(o2.amount) AS total_spend
      FROM orders o2
      JOIN customers c2 ON o2.customer_id = c2.customer_id
      WHERE c2.state = c.state
      AND o2.order_date BETWEEN '2023-01-01' AND '2023-12-31'
      GROUP BY c2.customer_id
    ) state_spend
  ) AS avg_state_spend,
  CASE
    WHEN SUM(o.amount) > (
      SELECT AVG(total_spend)
      FROM (
        SELECT SUM(o2.amount) AS total_spend
        FROM orders o2
        JOIN customers c2 ON o2.customer_id = c2.customer_id
        WHERE c2.state = c.state
        AND o2.order_date BETWEEN '2023-01-01' AND '2023-12-31'
        GROUP BY c2.customer_id
      ) state_spend
    ) THEN 'Above Average'
    ELSE 'Below Average'
  END AS spend_category
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.customer_id, c.name, c.state;

Expected output:
| customer_id | name | state | total_spend | avg_state_spend | spend_category | |-------------|-----------|-------|-------------|-----------------|-----------------| | 1 | Alice | CA | 1500.00 | 1250.50 | Above Average | | 2 | Bob | NY | 800.00 | 980.75 | Below Average |

Step 3: Optimize the query (avoid correlated subqueries where possible).

WITH state_avg AS (
  SELECT
    c.state,
    AVG(SUM(o.amount)) AS avg_state_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
  GROUP BY c.state, c.customer_id
  GROUP BY c.state
)
SELECT
  c.customer_id,
  c.name,
  c.state,
  SUM(o.amount) AS total_spend,
  s.avg_state_spend,
  CASE
    WHEN SUM(o.amount) > s.avg_state_spend THEN 'Above Average'
    ELSE 'Below Average'
  END AS spend_category
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN state_avg s ON c.state = s.state
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.customer_id, c.name, c.state, s.avg_state_spend;

Why this works:
- Replaced the correlated subquery with a CTE (Common Table Expression) for better readability and performance.
- The database executes the CTE once, not per row.

4. ? Production-Ready Best Practices

Performance

• Avoid correlated subqueries in WHERE clauses—they execute per row and can cripple performance. Use JOINs or CTEs instead.
• Use EXISTS instead of IN for large datasets—EXISTS stops at the first match, while IN fetches all values.
• Limit subquery results with LIMIT or TOP if you only need a few rows (e.g., WHERE product_id IN (SELECT product_id FROM top_products LIMIT 10)).

Readability

• Indent subqueries for clarity: sql SELECT
customer_id,
(SELECT MAX(order_date) FROM orders WHERE customer_id = c.customer_id) AS last_order_date FROM customers c;
• Use CTEs (WITH clauses) to break down complex subqueries into logical steps.
• Name subqueries in FROM clauses for debugging: sql SELECT * FROM (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id ) AS customer_totals;

Maintainability

• Document subqueries with comments if they’re non-obvious: sql -- Get the top 10% of customers by spend SELECT customer_id FROM customers WHERE total_spend > (
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_spend)
FROM customer_spend );
• Test subqueries in isolation before embedding them in larger queries.

Security

• Sanitize inputs if subqueries are built dynamically (e.g., in application code) to avoid SQL injection.
• Avoid SELECT * in subqueries—explicitly list columns to prevent schema changes from breaking your query.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Question Patterns

1. "Which subquery type is most efficient for filtering rows based on a dynamic condition?"
2. Answer: Non-correlated subquery (executes once).

3. Trap: Correlated subqueries are slower but sometimes necessary.

4. "What’s the difference between IN and EXISTS?"

5. Answer: EXISTS stops at the first match; IN fetches all values.

6. Trap: IN fails with NULL values.

7. "Rewrite this query to avoid a correlated subquery:"
   sql
SELECT name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

8. Answer:
   sql
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.name
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

9. "When would you use a subquery in FROM instead of a JOIN?"

10. Answer: When you need to pre-aggregate data before joining (e.g., monthly sales totals).

Key Trap Distinctions

• Correlated vs. Non-Correlated:
• Correlated = row-by-row (slow).
• Non-correlated = single execution (fast).
• IN vs. EXISTS:
• IN = value list (slower, fails with NULL).
• EXISTS = existence check (faster, handles NULL).
• ANY vs. ALL:
• ANY = matches any value (like OR).
• ALL = matches all values (like AND).

7. ? Hands-On Challenge

Challenge:

"Find all products that have never been ordered." Tables:
- products (product_id, product_name) - order_items (order_item_id, order_id, product_id)

Solution:

SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL);

OR (better, handles NULL):

SELECT product_id, product_name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items o WHERE o.product_id = p.product_id);

Why it works:
- NOT IN fails if order_items.product_id contains NULL.
- NOT EXISTS is safer and often faster.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Subquery Documentation – Official docs with examples.
2. SQLZoo Subquery Tutorial – Interactive exercises.
3. SQL Performance Explained (Markus Winand) – Deep dive into subquery optimization.
4. BigQuery Subquery Best Practices – Google’s guide for large datasets.