Derived Tables (Subqueries in FROM) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Scalar Subqueries, GROUP BY
Builds toward Common Table Expressions (CTEs), LATERAL Joins
What are Derived Tables in SQL?
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.
How do you write a subquery in the FROM clause?
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. That name comes from the alias after the closing parenthesis.
How do you filter on an aggregate without HAVING?
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:
SELECT COUNT(*) AS qualifying_customers FROM ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) AS customer_orders WHERE order_count >= 3
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.
Does a derived table need an alias in PostgreSQL?
The one thing that trips people up: leaving off the alias.
PostgreSQL 16 and later will run a derived table without one — delete AS customer_orders from the first query and it returns exactly the same rows. Write the alias anyway. The SQL standard requires one, PostgreSQL did too until version 16, and without a name you cannot write customer_orders.order_count, which you need as soon as you join the derived table to another table that shares a column name. Pick something descriptive that makes the query readable — AS customer_orders, AS status_averages — rather than a generic AS t.
Practice Derived Tables in SQL
Brightlane's CRM team is building a high-value customer list and needs to identify buyers with substantial order history.
Write a query to return the customer ID and total order count for every customer who has placed more than three orders.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - The threshold (
> 3) applies to the per-customer count. - Each customer's order count is computed first, then the per-customer counts are narrowed to those above the threshold.
Output:
- One row per qualifying customer, with columns
customer_idandorder_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Derived Tables 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.
Start learning to practice all 9 Derived Tables problems, with instant grading and mastery tracking.
Common questions about Derived Tables
Can a derived table see a column from the outer query?
No. A subquery in FROM is evaluated on its own, so referencing an outer table raises an invalid reference to a FROM-clause entry. Writing LATERAL before it lifts exactly that restriction, which is the whole reason LATERAL exists.
Can you nest a derived table inside another?
Yes, as deep as the logic needs. Each layer is a complete query with its own alias. Past two levels a chain of named CTEs usually reads better, because each step gets a name instead of another set of brackets.
Why does a derived table need its own alias?
So the outer query has a name to refer to. Without one you cannot qualify a column, which matters as soon as the derived table is joined to something that shares a column name. Give it a descriptive name rather than a single letter.
