++++Use nested SELECT queries and WITH common table expressions to break complex MySQL logic into readable steps.
11. Subqueries and CTEs
Subqueries and CTEs
Subqueries and CTEs let one query depend on the result of another query. They are useful when a problem is easier to solve in steps.
Scalar subquery
A scalar subquery returns one value.
SELECT
order_id,
total_amount
FROM orders
WHERE total_amount > (
SELECT AVG(total_amount)
FROM orders
);This returns orders whose value is above the overall average.
IN subquery
Use an IN subquery when the inner query returns a list.
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE status = "paid"
);This returns customers who have at least one paid order.
NOT IN and NULL caution
NOT IN can behave unexpectedly if the subquery returns NULL. A safer pattern is often NOT EXISTS.
SELECT *
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);This returns customers with no matching orders.
Derived table
A derived table is a subquery in the FROM clause.
SELECT
customer_id,
paid_total
FROM (
SELECT
customer_id,
SUM(total_amount) AS paid_total
FROM orders
WHERE status = "paid"
GROUP BY customer_id
) AS customer_totals
WHERE paid_total >= 500;CTE with WITH
A CTE names a temporary result set at the top of the query.
WITH customer_totals AS (
SELECT
customer_id,
SUM(total_amount) AS paid_total
FROM orders
WHERE status = "paid"
GROUP BY customer_id
)
SELECT
customer_id,
paid_total
FROM customer_totals
WHERE paid_total >= 500;CTEs usually read better than deeply nested derived tables.
Multiple CTEs
WITH paid_orders AS (
SELECT *
FROM orders
WHERE status = "paid"
),
customer_totals AS (
SELECT
customer_id,
SUM(total_amount) AS paid_total
FROM paid_orders
GROUP BY customer_id
)
SELECT *
FROM customer_totals
ORDER BY paid_total DESC;Use CTEs when each step has a clear meaning and you want the final query to tell a story.