Subquery in FROM (Derived Tables)
intermediatesubqueries
2 min read
What is a Subquery in FROM?
A subquery in the FROM clause creates a temporary, unnamed result set called a derived table (or inline view). The outer query treats it like a regular table.
Syntax
SELECT sub.column
FROM (
SELECT column, aggregate_function(col2) AS agg
FROM table_name
GROUP BY column
) AS sub
WHERE sub.agg > value;When to Use
- Filtering on aggregated results without HAVING
- Breaking complex queries into logical steps
- Reusing computed columns in the outer query
Key Points
- Alias Required — Every derived table must have an alias (AS sub).
- Scope — Columns from the derived table are accessed via the alias.
- One-Time Evaluation — The subquery is evaluated once, then the outer query reads from the result.
- CTE Alternative — Common Table Expressions (WITH) offer a named, reusable alternative.
- Optimization — PostgreSQL may flatten the subquery into the outer query during planning.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Subquery in FROM (Derived Tables) Challenge
Write a query that find courses with more than 5 enrollments.
Expected result
Courses with more than 5 enrollments showing course_id and count.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Subquery in FROM?
Initializing database...Each run starts from fresh sample data.
More Examples
Derived table with a join
Get average age per grade, then join back to students.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between a derived table and a CTE?
Both create temporary result sets. CTEs use WITH and are named, making them reusable and more readable. Derived tables are anonymous and inline.
Can I join two derived tables?
Yes. You can join multiple derived tables just like regular tables: FROM (SELECT ...) AS a JOIN (SELECT ...) AS b ON a.id = b.id.