Master advanced subquery techniques in SQL including correlated subqueries, scalar subqueries, and the EXISTS operator to solve complex data problems.
Master advanced subquery techniques in SQL including correlated subqueries, scalar subqueries, and the EXISTS operator to solve complex data problems.
Subqueries, also known as nested queries or inner queries, are SELECT statements embedded within another SQL statement.
They allow you to use the results of one query within another, enabling complex data operations that would be difficult
or impossible to express with simple queries.
Key Types of Subqueries:
Scalar Subquery
: Returns a single value (one row, one column)
Example use:
Selecting a single aggregate value or retrieving a specific property
Row Subquery
: Returns a single row with multiple columns
Example use:
Comparing multiple columns simultaneously
Column Subquery
: Returns a single column with multiple rows
Example use:
Filtering with IN, ANY, ALL operators
Table Subquery
: Returns multiple rows and columns
Example use:
Creating a derived table in the FROM clause
Correlated Subquery
: References columns from the outer query
Example use:
Row-by-row processing, finding related records
In this lesson, we'll explore advanced techniques for working with these subquery types to solve complex
data problems. We'll use the following sample database schema for our examples:
1-- Sample Customers table
2CREATE TABLE customers (
3 customer_id INT PRIMARY KEY,
4 name VARCHAR(100),
5 email VARCHAR(100),
6 city VARCHAR(100),
7 signup_date DATE
8);
9
10-- Sample Orders table
11CREATE TABLE orders (
12 order_id INT PRIMARY KEY,
13 customer_id INT,
14 order_date DATE,
15 total_amount DECIMAL(10,2),
16 status VARCHAR(20),
17 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
18);
19
20-- Sample Products table
21CREATE TABLE products (
22 product_id INT PRIMARY KEY,
23 name VARCHAR(100),
24 category VARCHAR(50),
25 price DECIMAL(10,2),
26 stock_quantity INT
27);
28
29-- Sample Order Items table (for order line items)
30CREATE TABLE order_items (
31 item_id INT PRIMARY KEY,
32 order_id INT,
33 product_id INT,
34 quantity INT,
35 price_each DECIMAL(10,2),
36 FOREIGN KEY (order_id) REFERENCES orders(order_id),
37 FOREIGN KEY (product_id) REFERENCES products(product_id)
38);
39
40-- Sample Employees table (for self-referential examples)
41CREATE TABLE employees (
42 employee_id INT PRIMARY KEY,
43 name VARCHAR(100),
44 title VARCHAR(100),
45 department VARCHAR(50),
46 manager_id INT,
47 salary DECIMAL(10,2),
48 hire_date DATE,
49 FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
50);Before diving into advanced techniques, let's briefly review basic subquery use cases:
1-- Find customers who have placed orders over $500
2SELECT
3 customer_id,
4 name,
5 email
6FROM customers
7WHERE customer_id IN (
8 SELECT customer_id
9 FROM orders
10 WHERE total_amount > 500
11);1-- List all products with their latest order date
2SELECT
3 p.product_id,
4 p.name,
5 p.category,
6 (
7 SELECT MAX(o.order_date)
8 FROM orders o
9 JOIN order_items oi ON o.order_id = oi.order_id
10 WHERE oi.product_id = p.product_id
11 ) AS latest_order_date
12FROM products p;1-- Calculate average order value by customer
2SELECT
3 c.customer_id,
4 c.name,
5 customer_orders.avg_order_value
6FROM customers c
7JOIN (
8 SELECT
9 customer_id,
10 AVG(total_amount) AS avg_order_value
11 FROM orders
12 GROUP BY customer_id
13) customer_orders ON c.customer_id = customer_orders.customer_id
14ORDER BY customer_orders.avg_order_value DESC;1-- Find product categories with higher than average order quantities
2SELECT
3 p.category,
4 AVG(oi.quantity) AS avg_quantity
5FROM products p
6JOIN order_items oi ON p.product_id = oi.product_id
7GROUP BY p.category
8HAVING AVG(oi.quantity) > (
9 SELECT AVG(quantity)
10 FROM order_items
11);Correlated subqueries are a powerful form of subquery that references columns from the outer query.
Unlike regular subqueries that execute once, correlated subqueries run once for each row processed by
the outer query, making them ideal for row-by-row analysis.
How Correlated Subqueries Work:
The database engine processes each row in the outer query
For each row, it executes the inner (correlated) subquery
The inner query references columns from the current row of the outer query
The result determines whether the current row is included in the final result set
Note: This row-by-row execution makes correlated subqueries powerful but potentially less efficient than
non-correlated alternatives. Always consider query optimization when using them.
The EXISTS operator is often used with correlated subqueries to check for the existence of related records.
It returns TRUE if the subquery returns any rows, and FALSE otherwise.
Unlike IN, which compares values, EXISTS only checks whether records exist, making it more efficient
for certain types of queries.
Example 1: Finding customers with orders
This query finds all customers who have placed at least one order. The SELECT 1 in the subquery is
a common pattern—since EXISTS only checks for the existence of rows, it doesn't matter what columns
you select.
Example 2: Finding customers without orders (NOT EXISTS)
This query finds all customers who haven't placed any orders. NOT EXISTS is the logical opposite of EXISTS.
Example 3: Finding products that have never been ordered
Example 4: Complex business rule with multiple EXISTS checks
This complex example uses multiple EXISTS clauses with correlated subqueries to identify VIP customers
based on several business rules. Each subquery checks a different condition, and all must be true
for a customer to be included in the results.
1-- Find all customers who have placed at least one order
2SELECT
3 customer_id,
4 name,
5 email
6FROM customers c
7WHERE EXISTS (
8 SELECT 1
9 FROM orders o
10 WHERE o.customer_id = c.customer_id
11);1-- Find all customers who haven't placed any orders
2SELECT
3 customer_id,
4 name,
5 email
6FROM customers c
7WHERE NOT EXISTS (
8 SELECT 1
9 FROM orders o
10 WHERE o.customer_id = c.customer_id
11);1-- Find all products that have never been ordered
2SELECT
3 product_id,
4 name,
5 category,
6 price
7FROM products p
8WHERE NOT EXISTS (
9 SELECT 1
10 FROM order_items oi
11 WHERE oi.product_id = p.product_id
12);1-- Find "VIP customers" who:
2-- 1. Have placed at least 3 orders
3-- 2. Have ordered from at least 2 different product categories
4-- 3. Have spent over $1000 total
5
6SELECT
7 c.customer_id,
8 c.name,
9 c.email
10FROM customers c
11WHERE
12 -- Condition 1: At least 3 orders
13 EXISTS (
14 SELECT 1
15 FROM (
16 SELECT customer_id, COUNT(*) AS order_count
17 FROM orders
18 WHERE customer_id = c.customer_id
19 GROUP BY customer_id
20 HAVING COUNT(*) >= 3
21 ) order_check
22 )
23 -- Condition 2: Orders from at least 2 different product categories
24 AND EXISTS (
25 SELECT 1
26 FROM (
27 SELECT COUNT(DISTINCT p.category) AS category_count
28 FROM orders o
29 JOIN order_items oi ON o.order_id = oi.order_id
30 JOIN products p ON oi.product_id = p.product_id
31 WHERE o.customer_id = c.customer_id
32 HAVING COUNT(DISTINCT p.category) >= 2
33 ) category_check
34 )
35 -- Condition 3: Total spending over $1000
36 AND EXISTS (
37 SELECT 1
38 FROM (
39 SELECT customer_id, SUM(total_amount) AS total_spent
40 FROM orders
41 WHERE customer_id = c.customer_id
42 GROUP BY customer_id
43 HAVING SUM(total_amount) > 1000
44 ) spending_check
45 );Correlated subqueries can also be used in the SELECT clause to calculate values for each row:
This query calculates, for each product, its sales as a percentage of the total sales in its category.
It uses multiple correlated subqueries in both the SELECT and CASE expressions.
1-- For each product, show its sales percentage relative to its category
2SELECT
3 p.product_id,
4 p.name,
5 p.category,
6 (
7 SELECT SUM(oi.quantity * oi.price_each)
8 FROM order_items oi
9 WHERE oi.product_id = p.product_id
10 ) AS product_sales,
11 (
12 SELECT SUM(oi.quantity * oi.price_each)
13 FROM order_items oi
14 JOIN products p2 ON oi.product_id = p2.product_id
15 WHERE p2.category = p.category
16 ) AS category_sales,
17 CASE
18 WHEN (
19 SELECT SUM(oi.quantity * oi.price_each)
20 FROM order_items oi
21 JOIN products p2 ON oi.product_id = p2.product_id
22 WHERE p2.category = p.category
23 ) > 0
24 THEN
25 ROUND(
26 (
27 SELECT SUM(oi.quantity * oi.price_each)
28 FROM order_items oi
29 WHERE oi.product_id = p.product_id
30 ) * 100.0 /
31 (
32 SELECT SUM(oi.quantity * oi.price_each)
33 FROM order_items oi
34 JOIN products p2 ON oi.product_id = p2.product_id
35 WHERE p2.category = p.category
36 ),
37 2
38 )
39 ELSE 0
40 END AS percent_of_category_sales
41FROM products p
42ORDER BY p.category, percent_of_category_sales DESC;Performance Tips:
Ensure proper indexing on join and filter columns
Consider using JOINs or CTEs as alternatives when appropriate
Use EXISTS instead of IN for correlated subqueries
Avoid unnecessary columns in the subquery (use SELECT 1 with EXISTS)
Limit the number of rows processed by filtering the outer query first
Here's an example of rewriting a correlated subquery using a JOIN, which might perform better in some cases:
1-- Correlated subquery version
2SELECT
3 c.customer_id,
4 c.name,
5 (
6 SELECT COUNT(*)
7 FROM orders o
8 WHERE o.customer_id = c.customer_id
9 ) AS order_count
10FROM customers c;
11
12-- JOIN version (potentially more efficient)
13SELECT
14 c.customer_id,
15 c.name,
16 COUNT(o.order_id) AS order_count
17FROM customers c
18LEFT JOIN orders o ON c.customer_id = o.customer_id
19GROUP BY c.customer_id, c.name;Scalar subqueries return a single value—one row, one column—making them ideal for calculations, comparisons,
and creating derived values. They can be used in various parts of a SQL statement, including SELECT, WHERE,
HAVING, and even in expressions like CASE statements.
This query uses a scalar subquery to calculate the company-wide average salary once,
then reuses that value to compare each department's average salary against it.
This correlated scalar subquery finds products with prices above the average for their specific category.
This query sorts customers by their most recent order date. The NULLS LAST clause ensures that
customers with no orders appear at the end.
This example uses scalar subqueries in a CASE expression to categorize employees based on how their salary
compares to their department's average.
1-- Show each department's average salary compared to company average
2SELECT
3 department,
4 AVG(salary) AS dept_avg_salary,
5 (SELECT AVG(salary) FROM employees) AS company_avg_salary,
6 ROUND(
7 AVG(salary) * 100.0 / (SELECT AVG(salary) FROM employees) - 100,
8 2
9 ) AS percent_diff_from_company_avg
10FROM employees
11GROUP BY department
12ORDER BY dept_avg_salary DESC;1-- Find products priced higher than the average price in their category
2SELECT
3 product_id,
4 name,
5 category,
6 price
7FROM products p
8WHERE price > (
9 SELECT AVG(price)
10 FROM products
11 WHERE category = p.category
12)
13ORDER BY category, price DESC;1-- Order customers by their most recent order date
2SELECT
3 customer_id,
4 name,
5 email
6FROM customers c
7ORDER BY (
8 SELECT MAX(order_date)
9 FROM orders
10 WHERE customer_id = c.customer_id
11) DESC NULLS LAST;1-- Categorize employees by salary relative to department average
2SELECT
3 employee_id,
4 name,
5 department,
6 salary,
7 (
8 SELECT AVG(salary)
9 FROM employees e2
10 WHERE e2.department = e1.department
11 ) AS dept_avg_salary,
12 CASE
13 WHEN salary > 1.5 * (
14 SELECT AVG(salary)
15 FROM employees e2
16 WHERE e2.department = e1.department
17 ) THEN 'High'
18 WHEN salary < 0.8 * (
19 SELECT AVG(salary)
20 FROM employees e2
21 WHERE e2.department = e1.department
22 ) THEN 'Low'
23 ELSE 'Average'
24 END AS salary_category
25FROM employees e1
26ORDER BY department, salary DESC;Scalar subqueries should normally return exactly one value. If a subquery returns no rows, it results in NULL.
If it returns multiple rows, it will cause an error. Here's how to handle these cases:
These examples show how to handle potential NULL results and ensure that scalar subqueries return
exactly one row to avoid errors.
1-- Using COALESCE to handle NULL results from scalar subqueries
2SELECT
3 product_id,
4 name,
5 COALESCE(
6 (
7 SELECT MAX(order_date)
8 FROM orders o
9 JOIN order_items oi ON o.order_id = oi.order_id
10 WHERE oi.product_id = p.product_id
11 ),
12 '2000-01-01' -- Default value if no orders exist
13 ) AS latest_order_date
14FROM products p;
15
16-- Ensuring single row results with aggregates
17SELECT
18 department,
19 (
20 SELECT MAX(salary) -- This guarantees a single result even if multiple employees have the highest salary
21 FROM employees
22 WHERE department = e.department
23 ) AS highest_salary_in_dept
24FROM employees e
25GROUP BY department;This query calculates cumulative sales by month. For each month, the subquery sums all sales from that month
and earlier. (Note: window functions are often more efficient for this specific case, but this demonstrates
the scalar subquery approach.)
This query ranks employees by salary within their department. For each employee, it counts how many
colleagues in the same department have higher salaries, then adds 1 to get their rank. (Again, window functions
might be more efficient, but this shows the scalar subquery technique.)
This example creates a pivot table showing sales by product category for each month. Each category column
is calculated using a separate scalar subquery. (Many databases offer built-in pivot functions, but this
approach works universally.)
1-- Calculate cumulative sales by month using scalar subqueries
2SELECT
3 DATE_TRUNC('month', order_date) AS month,
4 SUM(total_amount) AS monthly_sales,
5 (
6 SELECT SUM(total_amount)
7 FROM orders o2
8 WHERE DATE_TRUNC('month', o2.order_date) <= DATE_TRUNC('month', o1.order_date)
9 ) AS cumulative_sales
10FROM orders o1
11GROUP BY DATE_TRUNC('month', order_date)
12ORDER BY month;1-- Find each employee's salary rank within their department
2SELECT
3 employee_id,
4 name,
5 department,
6 salary,
7 (
8 SELECT COUNT(*) + 1
9 FROM employees e2
10 WHERE e2.department = e1.department
11 AND e2.salary > e1.salary
12 ) AS dept_salary_rank
13FROM employees e1
14ORDER BY department, dept_salary_rank;1-- Create a pivot table of sales by product category and month
2SELECT
3 DATE_TRUNC('month', o.order_date) AS month,
4 (
5 SELECT SUM(oi.quantity * oi.price_each)
6 FROM order_items oi
7 JOIN products p ON oi.product_id = p.product_id
8 JOIN orders o2 ON oi.order_id = o2.order_id
9 WHERE p.category = 'Electronics'
10 AND DATE_TRUNC('month', o2.order_date) = DATE_TRUNC('month', o.order_date)
11 ) AS electronics_sales,
12 (
13 SELECT SUM(oi.quantity * oi.price_each)
14 FROM order_items oi
15 JOIN products p ON oi.product_id = p.product_id
16 JOIN orders o2 ON oi.order_id = o2.order_id
17 WHERE p.category = 'Furniture'
18 AND DATE_TRUNC('month', o2.order_date) = DATE_TRUNC('month', o.order_date)
19 ) AS furniture_sales,
20 (
21 SELECT SUM(oi.quantity * oi.price_each)
22 FROM order_items oi
23 JOIN products p ON oi.product_id = p.product_id
24 JOIN orders o2 ON oi.order_id = o2.order_id
25 WHERE p.category = 'Appliances'
26 AND DATE_TRUNC('month', o2.order_date) = DATE_TRUNC('month', o.order_date)
27 ) AS appliances_sales
28FROM orders o
29GROUP BY DATE_TRUNC('month', o.order_date)
30ORDER BY month;SQL provides several operators designed to work specifically with subqueries. These operators—IN, ANY, and ALL—enable
powerful comparisons between a value and the results of a subquery that returns multiple rows.
Key Subquery Operators:
: Checks if a value matches ANY value in the subquery result
Equivalent to:
: Checks if a value matches NO value in the subquery result
Equivalent to:
!= ALL (with NULL handling differences)
: True if value is less than at least one result value
Less than the maximum value
: True if value is greater than at least one result value
Greater than the minimum value
: True if value is less than all result values
Less than the minimum value
: True if value is greater than all result values
Greater than the maximum value
The IN operator checks if a value matches any value in a list or subquery result. It's one of the most
common ways to use subqueries.
These examples show how to use IN with subqueries to find records that match a set of values returned by the subquery.
NOT IN and NULL Handling
Be careful with NOT IN when the subquery might return NULL values. If a subquery returns even one NULL,
NOT IN will not match any rows:
When dealing with potential NULL values, NOT EXISTS is often safer than NOT IN.
1-- Find products that have been ordered
2SELECT
3 product_id,
4 name,
5 category,
6 price
7FROM products
8WHERE product_id IN (
9 SELECT DISTINCT product_id
10 FROM order_items
11);
12
13-- Find orders with items from the 'Electronics' category
14SELECT
15 o.order_id,
16 o.order_date,
17 o.total_amount
18FROM orders o
19WHERE o.order_id IN (
20 SELECT oi.order_id
21 FROM order_items oi
22 JOIN products p ON oi.product_id = p.product_id
23 WHERE p.category = 'Electronics'
24);1-- This might not work as expected if there are NULLs in customer_id
2SELECT
3 customer_id,
4 name,
5 email
6FROM customers
7WHERE customer_id NOT IN (
8 SELECT customer_id
9 FROM orders
10 WHERE status = 'Completed'
11);
12
13-- Safer alternative using NOT EXISTS
14SELECT
15 customer_id,
16 name,
17 email
18FROM customers c
19WHERE NOT EXISTS (
20 SELECT 1
21 FROM orders o
22 WHERE o.customer_id = c.customer_id
23 AND o.status = 'Completed'
24);ANY and ALL extend comparison operators to work with subqueries that return multiple values:
value < ANY(subquery): True if value is less than at least one result from the subquery
value > ANY(subquery): True if value is greater than at least one result from the subquery
value < ALL(subquery): True if value is less than every result from the subquery
value > ALL(subquery): True if value is greater than every result from the subquery
Examples of ANY
The first query finds products priced higher than at least one Furniture product. The second query finds
employees with a salary higher than at least one Finance department employee.
Examples of ALL
The first query finds products priced higher than ALL Appliances products (i.e., more expensive than the most expensive
Appliance). The second query finds employees with salaries lower than ALL Executive department employees
(i.e., lower than the lowest-paid executive).
1-- Find products priced higher than any product in the 'Furniture' category
2SELECT
3 product_id,
4 name,
5 category,
6 price
7FROM products
8WHERE price > ANY (
9 SELECT price
10 FROM products
11 WHERE category = 'Furniture'
12)
13ORDER BY price;
14
15-- Find employees with a salary higher than any employee in the 'Finance' department
16SELECT
17 employee_id,
18 name,
19 department,
20 salary
21FROM employees
22WHERE salary > ANY (
23 SELECT salary
24 FROM employees
25 WHERE department = 'Finance'
26)
27ORDER BY salary;1-- Find products priced higher than ALL products in the 'Appliances' category
2SELECT
3 product_id,
4 name,
5 category,
6 price
7FROM products
8WHERE price > ALL (
9 SELECT price
10 FROM products
11 WHERE category = 'Appliances'
12)
13ORDER BY price;
14
15-- Find employees with a salary lower than ALL employees in the 'Executive' department
16SELECT
17 employee_id,
18 name,
19 department,
20 salary
21FROM employees
22WHERE salary < ALL (
23 SELECT salary
24 FROM employees
25 WHERE department = 'Executive'
26)
27ORDER BY salary DESC;This complex query finds salespeople who have sold more than the average in ALL regions—truly exceptional performers.
(Note: This example uses a hypothetical schema extension with regions and sales rep assignments.)
These queries find warehouses that stock at least one product from each category in our catalog.
The first uses NOT EXISTS to check that there isn't any category missing from the warehouse.
The second uses ALL to check that the number of distinct categories in the warehouse matches the total number of categories.
1-- Find salespeople who have sold more than the average in all regions
2SELECT
3 e.employee_id,
4 e.name,
5 e.department,
6 (
7 SELECT SUM(o.total_amount)
8 FROM orders o
9 WHERE o.customer_id IN (
10 -- Assuming customers are assigned to employees as their sales rep
11 SELECT customer_id
12 FROM customers
13 WHERE sales_rep_id = e.employee_id
14 )
15 ) AS total_sales
16FROM employees e
17WHERE e.department = 'Sales'
18 AND (
19 SELECT SUM(o.total_amount)
20 FROM orders o
21 WHERE o.customer_id IN (
22 SELECT customer_id
23 FROM customers
24 WHERE sales_rep_id = e.employee_id
25 )
26 ) > ALL (
27 -- All regional average sales per salesperson
28 SELECT AVG(sales_per_person)
29 FROM (
30 SELECT
31 r.region_id,
32 AVG(
33 (
34 SELECT SUM(o.total_amount)
35 FROM orders o
36 WHERE o.customer_id IN (
37 SELECT customer_id
38 FROM customers
39 WHERE sales_rep_id = e2.employee_id
40 )
41 )
42 ) AS sales_per_person
43 FROM regions r
44 JOIN employees e2 ON e2.region_id = r.region_id
45 WHERE e2.department = 'Sales'
46 GROUP BY r.region_id
47 ) regional_averages
48 );1-- Find warehouses that stock at least one item from each product category
2SELECT
3 w.warehouse_id,
4 w.name,
5 w.city
6FROM warehouses w
7WHERE NOT EXISTS (
8 -- Check if there's any category NOT represented in this warehouse
9 SELECT 1
10 FROM (
11 SELECT DISTINCT category
12 FROM products
13 ) categories
14 WHERE NOT EXISTS (
15 SELECT 1
16 FROM inventory i
17 JOIN products p ON i.product_id = p.product_id
18 WHERE i.warehouse_id = w.warehouse_id
19 AND p.category = categories.category
20 )
21);
22
23-- Alternative using ALL
24SELECT
25 w.warehouse_id,
26 w.name,
27 w.city
28FROM warehouses w
29WHERE (
30 SELECT COUNT(DISTINCT p.category)
31 FROM inventory i
32 JOIN products p ON i.product_id = p.product_id
33 WHERE i.warehouse_id = w.warehouse_id
34) = ALL (
35 SELECT COUNT(DISTINCT category)
36 FROM products
37);Now that we've covered the fundamentals of advanced subqueries, let's explore some complex real-world applications
and best practices to ensure your subqueries are both effective and efficient.
This complex example performs a cohort analysis, tracking customer retention by month since their first purchase.
It uses Common Table Expressions (CTEs) instead of nested subqueries for better readability, but demonstrates
the kind of advanced analysis you can perform with subqueries.
This advanced example uses subqueries and statistical analysis to identify potentially fraudulent orders
by looking for values that are statistically unusual for a particular customer or compared to all customers.
1-- Monthly cohort retention analysis
2WITH
3cohorts AS (
4 -- Define cohorts by first purchase month
5 SELECT
6 customer_id,
7 DATE_TRUNC('month', MIN(order_date)) AS cohort_month
8 FROM orders
9 GROUP BY customer_id
10),
11customer_activity AS (
12 -- Calculate months since first purchase
13 SELECT
14 c.customer_id,
15 c.cohort_month,
16 DATE_TRUNC('month', o.order_date) AS activity_month,
17 (EXTRACT(YEAR FROM o.order_date) - EXTRACT(YEAR FROM c.cohort_month)) * 12 +
18 (EXTRACT(MONTH FROM o.order_date) - EXTRACT(MONTH FROM c.cohort_month)) AS months_since_first_purchase
19 FROM cohorts c
20 JOIN orders o ON c.customer_id = o.customer_id
21),
22cohort_sizes AS (
23 -- Count customers in each cohort
24 SELECT
25 cohort_month,
26 COUNT(DISTINCT customer_id) AS cohort_size
27 FROM cohorts
28 GROUP BY cohort_month
29)
30-- Final retention pivot table
31SELECT
32 ca.cohort_month,
33 cs.cohort_size,
34 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 0 THEN ca.customer_id END) AS month_0,
35 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 1 THEN ca.customer_id END) AS month_1,
36 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 2 THEN ca.customer_id END) AS month_2,
37 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 3 THEN ca.customer_id END) AS month_3,
38 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 4 THEN ca.customer_id END) AS month_4,
39 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 5 THEN ca.customer_id END) AS month_5,
40 COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 6 THEN ca.customer_id END) AS month_6,
41 ROUND(COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 1 THEN ca.customer_id END) * 100.0 / cs.cohort_size, 1) AS month_1_retention_pct,
42 ROUND(COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 3 THEN ca.customer_id END) * 100.0 / cs.cohort_size, 1) AS month_3_retention_pct,
43 ROUND(COUNT(DISTINCT CASE WHEN ca.months_since_first_purchase = 6 THEN ca.customer_id END) * 100.0 / cs.cohort_size, 1) AS month_6_retention_pct
44FROM customer_activity ca
45JOIN cohort_sizes cs ON ca.cohort_month = cs.cohort_month
46GROUP BY ca.cohort_month, cs.cohort_size
47ORDER BY ca.cohort_month;1-- Detect potentially fraudulent orders based on unusual patterns
2WITH
3customer_avg_order AS (
4 -- Calculate average order value and standard deviation for each customer
5 SELECT
6 customer_id,
7 AVG(total_amount) AS avg_order_value,
8 STDDEV(total_amount) AS stddev_order_value,
9 COUNT(*) AS order_count
10 FROM orders
11 GROUP BY customer_id
12),
13company_avg_order AS (
14 -- Calculate company-wide averages
15 SELECT
16 AVG(total_amount) AS avg_order_value,
17 STDDEV(total_amount) AS stddev_order_value
18 FROM orders
19)
20-- Identify suspicious orders
21SELECT
22 o.order_id,
23 o.customer_id,
24 c.name AS customer_name,
25 o.order_date,
26 o.total_amount,
27 cao.avg_order_value AS customer_avg_order,
28 cao.stddev_order_value AS customer_stddev,
29 coag.avg_order_value AS company_avg_order,
30 CASE
31 -- Use customer's own history if they have enough orders
32 WHEN cao.order_count >= 5 THEN
33 (o.total_amount - cao.avg_order_value) / NULLIF(cao.stddev_order_value, 0)
34 -- Otherwise use company-wide metrics
35 ELSE
36 (o.total_amount - coag.avg_order_value) / NULLIF(coag.stddev_order_value, 0)
37 END AS z_score,
38 -- Flag for orders outside 3 standard deviations
39 CASE
40 WHEN cao.order_count >= 5 AND (o.total_amount - cao.avg_order_value) / NULLIF(cao.stddev_order_value, 0) > 3 THEN 'Unusual for this customer'
41 WHEN cao.order_count < 5 AND (o.total_amount - coag.avg_order_value) / NULLIF(coag.stddev_order_value, 0) > 3 THEN 'Unusual compared to all customers'
42 ELSE 'Normal'
43 END AS status,
44 -- Flag for additional fraud indicators
45 (
46 SELECT COUNT(*)
47 FROM order_items oi
48 WHERE oi.order_id = o.order_id
49 AND oi.quantity > 10
50 ) > 0 AS has_high_quantity_items
51FROM orders o
52JOIN customers c ON o.customer_id = c.customer_id
53LEFT JOIN customer_avg_order cao ON o.customer_id = cao.customer_id
54CROSS JOIN company_avg_order coag
55WHERE
56 -- Filter for statistically abnormal orders
57 (
58 (cao.order_count >= 5 AND (o.total_amount - cao.avg_order_value) / NULLIF(cao.stddev_order_value, 0) > 3)
59 OR
60 (cao.order_count < 5 AND (o.total_amount - coag.avg_order_value) / NULLIF(coag.stddev_order_value, 0) > 3)
61 )
62 -- Additional risk factors
63 OR o.order_date BETWEEN '2023-01-01' AND '2023-01-07' -- Holiday period
64 OR (
65 SELECT COUNT(*)
66 FROM order_items oi
67 WHERE oi.order_id = o.order_id
68 AND oi.quantity > 10
69 ) > 0 -- High quantity of any single item
70ORDER BY z_score DESC;Performance Best Practices:
Use appropriate indexes on columns referenced in subqueries and joins
Consider using JOINs or CTEs instead of correlated subqueries where possible
Filter data early to reduce the rows processed by subqueries
Use EXISTS instead of IN for checking existence when you don't need the actual values
Be cautious with nested subqueries—they can lead to exponential performance degradation
Consider materializing frequently used subquery results in a temporary table or CTE
Use EXPLAIN or EXPLAIN ANALYZE to understand query execution plans
Limit the columns returned in subqueries to only those you need
Rewriting Subqueries for Better Performance
This example shows how to rewrite a query that uses correlated subqueries, potentially improving
performance by avoiding repeated execution of the subquery.
1-- Original query with correlated subquery
2SELECT
3 c.customer_id,
4 c.name,
5 (
6 SELECT COUNT(*)
7 FROM orders o
8 WHERE o.customer_id = c.customer_id
9 ) AS order_count
10FROM customers c
11WHERE (
12 SELECT COUNT(*)
13 FROM orders o
14 WHERE o.customer_id = c.customer_id
15) > 5;
16
17-- Optimized version using JOIN
18SELECT
19 c.customer_id,
20 c.name,
21 order_counts.order_count
22FROM customers c
23JOIN (
24 SELECT
25 customer_id,
26 COUNT(*) AS order_count
27 FROM orders
28 GROUP BY customer_id
29 HAVING COUNT(*) > 5
30) order_counts ON c.customer_id = order_counts.customer_id;
31
32-- Or even better with CTE
33WITH order_counts AS (
34 SELECT
35 customer_id,
36 COUNT(*) AS order_count
37 FROM orders
38 GROUP BY customer_id
39 HAVING COUNT(*) > 5
40)
41SELECT
42 c.customer_id,
43 c.name,
44 oc.order_count
45FROM customers c
46JOIN order_counts oc ON c.customer_id = oc.customer_id;Different SQL problems can be solved with different subquery techniques. Here's a guide to help you choose:
Recommended Technique
When to Use
Checking if records exist
When you only need to know if matching records exist
Filtering against a list of values
When matching against a discrete set of values
Comparing with extreme values
For comparisons with maximum or minimum values
Calculating derived values
Scalar subquery in SELECT
When you need to include calculated values in results
Creating derived tables
FROM clause subquery
When working with aggregated or complex intermediate results
Finding matches/mismatches
EXISTS/NOT EXISTS
When looking for records that do/don't have related records
Complex multi-step logic
CTEs (WITH clause)
When breaking down complex queries into readable, logical steps
Use CTEs for complex queries
: The WITH clause creates named subqueries that make your
SQL more readable and self-documenting.
Add meaningful aliases
: Give subqueries and their columns descriptive names that convey their purpose.
Comment your SQL code
: Especially for complex subqueries, explain your logic and what each part does.
Format your SQL
: Use consistent indentation and line breaks to make nested queries easier to understand.
Break down complex logic
: Instead of one massive query, consider using temporary tables or CTEs to
divide complex logic into manageable, testable pieces.
Keep subqueries focused
: Each subquery should have a single, clear purpose.
Test subqueries independently
: Before embedding a subquery, test it separately to verify it returns the expected results.
While advanced subquery features are part of the SQL standard, support and syntax can vary across database systems:
PostgreSQL
: Excellent support for all features discussed in this lesson.
: Full support in modern versions, but older versions (before 5.6) had limited subquery optimization.
SQL Server
: Good support, but has unique optimizations for EXISTS vs. IN.
: Good support, but uses MINUS instead of EXCEPT for set difference operations.
: Basic support, but may have performance issues with complex subqueries.
Always test your complex subqueries with realistic data volumes on your specific database system,
as performance characteristics can vary significantly.
Master the art of advanced subqueries in SQL to solve complex data challenges. Learn how to use correlated subqueries,
scalar subqueries, and advanced operators like EXISTS, IN, ANY, and ALL to write powerful, flexible queries that
can handle sophisticated business requirements.
Your progress is saved in this browser, on this device only.