Part II: Core Data Analysis — MySQL Subqueries
What Is a Subquery?
A subquery is a query nested inside another SQL query. Subqueries help you perform complex
data analysis by allowing you to use the result of one query as input for another. They're
essential for filtering, aggregating, and comparing data in analytics tasks.
Types of Subqueries in MySQL
Scalar Subquery: Returns a single value (number, date, string). Useful for comparisons and
filtering.
Row Subquery: Returns a single row with multiple columns. Used for multi-column
comparisons.
Table Subquery: Returns a set of rows and columns (temporary table). Useful for
aggregating or preparing data before final selection.
Correlated Subquery: Uses values from the outer query's current row, running once for
each row. Enables row-by-row comparisons. [1] [2]
Where Can You Use Subqueries?
SELECT clause
WHERE clause
FROM clause
JOIN conditions
Practical Examples
Let's use the following sample tables:
orders
order_id customer_id order_date total_cost
1 1 2023-01-01 200.00
2 2 2023-01-02 150.00
3 3 2023-01-03 300.00
order_items
item_id order_id product_id quantity price_per_unit
1 1 101 2 50.00
2 1 102 1 100.00
3 2 101 1 150.00
4 3 103 3 100.00
1. Subquery in SELECT Clause
Find each order's total number of items:
SELECT order_id,
(SELECT SUM(quantity) FROM order_items WHERE order_items.order_id = orders.order_id) AS
FROM orders;
This returns each order with the sum of its item quantities. [1]
2. Subquery in WHERE Clause
Find orders made after the earliest order date for customer 1:
SELECT order_id, total_cost
FROM orders
WHERE order_date > (SELECT MIN(order_date) FROM orders WHERE customer_id = 1);
This filters orders to those after customer 1's first order date. [1]
3. Subquery in FROM Clause
Get each order's total cost and number of items:
SELECT o.order_id, o.total_cost, ai.total_items
FROM orders AS o
JOIN (
SELECT order_id, COUNT(*) AS total_items
FROM order_items
GROUP BY order_id
) AS ai ON o.order_id = ai.order_id;
Here, the subquery creates a temporary table of item counts per order, which is then joined to
the main table. [3] [1]
4. Subquery in JOIN Condition
List items from orders with cost above the average:
SELECT o.order_id, o.total_cost, oi.product_id, [Link]
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
AND o.total_cost > (SELECT AVG(total_cost) FROM orders);
This joins only orders whose total cost is above the average. [1]
5. Correlated Subquery Example
Find sales reps who closed deals above their region's average:
SELECT sr.rep_name
FROM sales_reps sr
WHERE sr.deal_value > (
SELECT AVG(deal_value)
FROM sales
WHERE region_id = sr.region_id
);
The subquery uses the outer query's region_id for comparison. [1]
Quick Review
Subqueries can be used in SELECT, WHERE, FROM, and JOIN clauses.
Scalar, row, table, and correlated subqueries each serve different analytic needs.
Subqueries help filter, aggregate, and compare data efficiently.
Practice:
Try writing a query to list all customers whose first order amount is above the average first order
amount for all customers. If you get stuck, share your attempt and I'll help you troubleshoot or
refine it.
⁂
1. [Link]
2. [Link]
3. [Link]
4. [Link]
5. [Link]
6. [Link]
7. [Link]
8. [Link]
