Subquery in FROM
In this page:
SELECT alias.column
FROM (SELECT columns FROM table_name) AS alias;What is a Subquery in FROM?
A subquery in the FROM clause is a query nested inside another, standing in for a real table. It acts like a temporary table. You can select data from it just like any regular table, joining or filtering it further in the outer query.
Example: What is a Subquery in FROM?
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 50), (2, 1, 70), (3, 2, 20);
SELECT * FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) AS totals;Using Table Aliases
In MySQL, you must always give an alias to a subquery in the FROM clause, unlike subqueries used elsewhere. This gives the temporary table a name so you can reference it and its columns from the outer query.
Example: Using Table Aliases
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 50), (2, 1, 70);
SELECT totals.customer_id, totals.spend
FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) AS totals;Aggregating Aggregates
You cannot easily nest aggregate functions directly, such as calling AVG() on the result of a SUM(). A subquery in the FROM clause helps you aggregate data that is already aggregated, by treating the first aggregation's output as a fresh table to summarize again.
Example: Aggregating Aggregates
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 50), (2, 1, 70), (3, 2, 20), (4, 2, 30);
SELECT AVG(spend) AS avg_customer_spend
FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) AS totals;Filtering inside the Subquery
Filtering inside the nested subquery, rather than after it in the outer query, can optimize performance. It reduces the number of rows the outer query needs to process, especially when the inner filter can use an index effectively.
Example: Filtering inside the Subquery
CREATE TABLE orders (id INT, status TEXT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 'paid', 1, 50), (2, 'unpaid', 1, 70);
SELECT customer_id, SUM(total) AS paid_total
FROM (SELECT * FROM orders WHERE status = 'paid') AS paid_orders
GROUP BY customer_id;Subqueries vs CTEs
Common Table Expressions (CTEs), written with WITH, work like subqueries in the FROM clause but with a name defined up front and better readability for complex queries. Subqueries are great for simple, quick, one-off logic blocks that don't need to be referenced more than once.
Example: Subqueries vs CTEs
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 50), (2, 1, 70);
-- Subquery in FROM
SELECT * FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) AS totals;
-- Equivalent CTE
WITH totals AS (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id)
SELECT * FROM totals;- Forgetting the required alias for a derived table, which gives
Every derived table must have its own alias. - Referring to a column inside the subquery that is not selected in it.
- Filtering after the subquery when it should be filtered inside, making MySQL process more rows.
Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: