Derived Tables (Subqueries in FROM) in SQL
A subquery in the FROM clause produces a named result set that the outer query treats as a table. The inner query executes completely before the outer query begins, making the inner query's output the sole source the outer query works from.
Before this Scalar Subqueries, GROUP BY
Builds toward Common Table Expressions (CTEs), LATERAL Joins
A derived table is a subquery written in the FROM clause and treated as a temporary table for the rest of the query.
You want to find customers who placed more than 3 orders. You know how to count orders per customer with GROUP BY. But you can't filter on COUNT(*) directly in a WHERE clause — WHERE runs before aggregation, before any counts exist. HAVING filters at the aggregation layer, but it's not designed for everything you might want to do with grouped results. A derived table solves this by letting you run the aggregation in an inner query and then write a plain WHERE filter on the result in the outer query.
The structure: write the aggregation in parentheses, give it an alias, and treat it like a table:
SELECT customer_id, order_count
FROM (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
) AS customer_orders
WHERE order_count > 3The inner query runs first. It produces one row per customer with their order count. The outer query treats that result as a table called customer_orders and filters it with WHERE. The alias after the closing parenthesis is required — without it, SQL raises a syntax error.
This two-layer structure is the core use case: the inner query shapes or summarizes the data, and the outer query applies further logic to that shaped result. Aggregating first and filtering second is the most common pattern:
SELECT category_id, total_value
FROM (
SELECT category_id, SUM(price) AS total_value
FROM products
GROUP BY category_id
) AS category_totals
WHERE total_value > 2000Categories with total product value above $2,000. The outer query can only reference columns that the inner query explicitly selected. Here that's category_id and total_value — not the underlying price column.
Derived tables compose with other query features. You can aggregate the derived table's output in the outer query:
The outer query counts how many customers qualify. The inner query determines which ones do. Each layer handles one step of the logic.
You can also combine derived tables with scalar subqueries to make comparisons against computed thresholds:
SELECT status, avg_order_value
FROM (
SELECT status, AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY status
) AS status_averages
WHERE avg_order_value > (SELECT AVG(total_amount) FROM orders)Order statuses whose average order value exceeds the overall average. The inner query computes the per-status average; the scalar subquery computes the overall average; the outer WHERE compares the two.
The one thing that trips people up: forgetting the alias.
A derived table must have an alias immediately after its closing parenthesis. FROM (...) WHERE ... without an alias fails. The alias is how the outer query identifies the derived table and references its columns. Pick something descriptive that makes the query readable — AS customer_orders, AS status_averages — rather than a generic AS t.
9 Derived Tables (Subqueries in FROM) practice problems
Write a query to return the customer ID and total order count for every customer who has placed **more than three** orders.
Write a query to return the category ID and total list price for every category whose combined product prices exceed `$2,000`.
Write a query to return the department ID and employee count for every department with **more than five** employees on record.
Write a query to return that count in a single column named `qualifying_customers`.
Write a query to return the category ID and product count for every assigned category that contains **more than five** products.
Write a query to return each qualifying status and its average order value.
Write a query to return each such employee's ID alongside their salary-record count (which will be `1`).
Write a query to return the category ID and product count for every category that contains **at least three** products.
Write a query to return the customer ID and personal average order value for every qualifying customer.
These problems are part of the Derived Tables (Subqueries in FROM) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.