Week 22 - SQL Chips and Chunks Newsletter

1. SQL Chip

A subquery is like a question within a question. It's a SELECT statement nested inside another SQL statement. Just like asking "What's the highest price?" before asking "Which products cost more than the highest price?", subqueries let you use the result of one query to help answer another.

2. SQL Chunk

Let's find all products that cost more than the average product price. First, we need to know the average. We can get that number with a subquery, then use it in our main query.

SELECT
    product_name,
    unit_price
FROM
    products
WHERE
    unit_price > (SELECT AVG(unit_price) FROM products);

3. SQL Challenge

Subqueries aren't just for the WHERE clause. You can also use them to create a new column in your main query. This is a powerful way to add a calculated value, like the average product price, to every row of your result set for context.

SELECT
    product_name,
    unit_price,
    (SELECT AVG(unit_price) FROM products) AS average_price
FROM
    products;

4. SQL Mistake

A common mistake is forgetting that subqueries must return a single column when used to filter with operators like =, <, or >. If the subquery returns more than one value, the outer query won't know which one to compare against, leading to an error.

-- SQL Mistake Example: Subquery returns multiple product IDs
SELECT
    product_name
FROM
    products
WHERE
    product_id = (SELECT product_id FROM products WHERE unit_price > 100);
-- This will return an error because the subquery returns more than one product_id

-- The FIX: Use the IN operator, which can handle multiple values
SELECT
    product_name
FROM
    products
WHERE
    product_id IN (SELECT product_id FROM products WHERE unit_price > 100);

5. SQL in Practice

In e-commerce, a common task is to find all customers who have placed an order in the last 30 days. This requires a subquery to first identify the most recent order date from the orders table and then use that date to filter the customers table.

SELECT
    customer_name,
    customer_email
FROM
    customers
WHERE
    last_order_date > (
        SELECT MAX(order_date) - INTERVAL '30' DAY
        FROM orders
    );

(Here, the subquery finds the latest order date, and the main query uses that to find customers who have ordered within the last 30 days.)

6. SQL Resource

SQL Subqueries (Nested Queries) - A Complete Guide by SQLBolt https://sqlbolt.com/lesson/subqueries_with_in_operator SQLBolt provides a hands-on, interactive tutorial for subqueries. It starts with the basics and gradually introduces more complex use cases, including subqueries in the WHERE and FROM clauses. The best part is that you can run the code directly on the website, allowing you to experiment and see the results instantly. This is an excellent way to solidify your understanding through practice.