Scalar and IN Subqueries
A subquery is a query within a query. It returns a result that the outer query uses. The simplest type is a scalar subquery, which returns a single value β like an average, count, or maximum.
Q1: Scalar Subquery β Above Average Spend
Find all customers who spent more than the average customer spending:
Breakdown:
- The inner query
(SELECT AVG(lifetime_spend) FROM customers)calculates the average spending across all customers. It returns a single number. - The outer query uses that number to filter customers whose spending exceeds the average.
- This is faster and clearer than calculating the average manually or using a JOIN.
Q2: IN Subquery β Products with Reviews
Find all products that have at least one review:
Breakdown:
- The subquery
(SELECT DISTINCT product_id FROM reviews)returns a list of product IDs that have reviews. - The IN operator checks if the outer query's product_id is in that list.
- Use IN when your subquery returns multiple values (a list). Use a scalar subquery (no IN) when it returns one value.
Tip: The DISTINCT keyword removes duplicates from the subquery result. This makes the IN check more efficient.
NOT IN and EXISTS
Sometimes you want the opposite: rows that don't match. There are two approaches: NOT IN and EXISTS. They have different performance characteristics and NULL-handling behaviors.
Q3: NOT IN β Products Never Ordered
Find products that have never been ordered:
Breakdown:
- The subquery finds all product IDs that appear in order_items.
- NOT IN filters to products whose ID is not in that list.
- Gotcha: NOT IN fails silently if the subquery contains NULL values. It returns no rows because NULL is never equal to anything (even itself). Always use DISTINCT and ensure no NULLs, or use EXISTS instead.
Q4: EXISTS β Better for "Does Any Row Match?"
Find customers who have placed at least one order. Use EXISTS for efficiency:
Breakdown:
- EXISTS checks if the subquery returns any row at all. It doesn't matter what columns it returnsβwe use SELECT 1 as a dummy column.
- EXISTS stops as soon as it finds a matching row (efficient for large tables).
- The subquery is correlatedβit references the outer query's customer_id. More on this below.
- Use EXISTS when you need "does any row match?" logic. Use IN for "is this value in a list?"
Performance Note: For large datasets, EXISTS often outperforms NOT IN. EXISTS stops at the first match; NOT IN must check all values. Also, EXISTS handles NULLs gracefully.
Your First CTE
A CTE (Common Table Expression) is a temporary result set that you define and use within a single query. It's created with the WITH clause and improves readability dramatically.
Q6: Simple CTE β Monthly Revenue
Calculate monthly revenue and show each month's percentage of total revenue:
Breakdown:
- The WITH clause creates a temporary table called monthly_revenue with columns month and revenue.
- The main query then selects from monthly_revenue as if it were a real table.
- The window function SUM(revenue) OVER () sums all revenue across all months, allowing us to calculate the percentage.
- CTEs are reusable in the same query and much more readable than nested subqueries.
Syntax Rule: The CTE is created with WITH, followed by the CTE name, AS, the query in parentheses. After the CTE definition, use it like a normal table in the SELECT.
Multiple CTEs and Complex Logic
You can chain multiple CTEs together. Each CTE can reference the ones defined before it. This lets you break complex queries into readable, logical steps.
Q7: Seller Ranking with Multiple CTEs
Calculate total revenue per seller, then rank them:
Breakdown:
- First CTE (seller_revenue) joins sellers and order_items to calculate total revenue per seller.
- Second CTE (seller_rank) references the first CTE and adds a RANK() window function.
- The main query then filters to the top 10 sellers.
- This step-by-step approach is far more readable than one massive nested query.
Q8: Customer Segmentation with CTEs
Segment customers by spending: VIP (>1000), Regular (>500), or New (<=500):
Breakdown:
- The CTE joins customers and orders, counting orders and summing spending.
- The main query adds a CASE statement to assign segments based on total_spent.
- COALESCE(SUM(...), 0) returns 0 if a customer has no orders (avoids NULL).
- This query is clean, maintainable, and easy to modify.
When to Use CTEs vs Subqueries: Use CTEs for readability and when you reference the same subquery multiple times. Use subqueries for small, one-off calculations. CTEs are better for senior-level SQL code.
Test Your Knowledge
Subqueries and CTEs β your SQL just levelled up.
Join the Best AI Program: The AI-Era Data Engineer β 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer β