Subqueries do not live only in WHERE. You can put them in SELECT (to add a calculated column) and in FROM (to create a temporary table on the fly). These are advanced patterns that make complex reporting queries possible.
A subquery in SELECT runs for each row and adds a column to the output. It is like a correlated subquery — it references the outer table to calculate something per row.
-- Show each customer with their total order count
SELECT
c.customer_id,
c.first_name,
c.last_name,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS total_orders,
(
SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS last_order_date
FROM customers c;
-- Compare each order amount to the overall average
SELECT
order_id,
total_amount,
(SELECT AVG(total_amount) FROM orders) AS avg_amount,
total_amount - (SELECT AVG(total_amount) FROM orders) AS diff_from_avg
FROM orders
ORDER BY diff_from_avg DESC;A subquery in FROM creates a temporary result set — a "derived table." You can query it like a regular table. This is useful when you need to aggregate first and then filter or join the aggregated results.
-- Step 1: Calculate each customer's average order value
-- Step 2: Find customers whose average is above 200
SELECT
customer_summary.customer_id,
customer_summary.avg_order_value,
customer_summary.total_orders
FROM (
SELECT
customer_id,
AVG(total_amount) AS avg_order_value,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
) AS customer_summary
WHERE customer_summary.avg_order_value > 200;
-- Join a derived table with another table
SELECT
c.first_name,
c.last_name,
cs.total_spent,
cs.order_count
FROM customers c
INNER JOIN (
SELECT
customer_id,
SUM(total_amount) AS total_spent,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
) AS cs ON c.customer_id = cs.customer_id
WHERE cs.total_spent > 1000
ORDER BY cs.total_spent DESC;Every derived table in FROM must have an alias. Writing FROM (SELECT ...) without AS alias_name will throw a syntax error. This is the most common mistake with subqueries in FROM.
If you find yourself writing deeply nested subqueries (3+ levels), consider using CTEs (Common Table Expressions) with the WITH keyword instead. CTEs are easier to read and debug. We will cover those in a later chapter.
Q: What is a derived table?
A: A derived table is a subquery in the FROM clause. It runs first, produces a result set, and the outer query treats it as a regular table. It must have an alias. Derived tables are useful when you need to aggregate data first (GROUP BY) and then filter or join the aggregated results. They exist only for the duration of the query — nothing is saved to disk.
Key Point: Subqueries in SELECT add calculated columns per row. Subqueries in FROM (derived tables) create temporary result sets. Derived tables must always have an alias.