Subquery in FROM
A subquery in the FROM clause creates a temporary inline table (derived table). The outer query then works with this table just like a real one. This powerful technique lets you pre-aggregate, pre-filter, or reshape data before the outer query sees it.
Derived Table Rules
- Must be aliased: FROM (SELECT ...) AS alias_name
- Acts like a real table - outer query can SELECT, WHERE, ORDER BY on its columns
- Non-correlated - runs once, result is reused
- In PostgreSQL/modern SQL, CTEs (WITH clause) are a cleaner alternative
- Use to pre-aggregate then filter on aggregate: GROUP BY then compare
Derived Table Examples
-- Pre-aggregate, then filter (can't use aggregate alias in HAVING directly in some cases)
SELECT order_totals.user_id, order_totals.total_spent
FROM (
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
) AS order_totals
WHERE order_totals.total_spent > 500;
-- Equivalent to HAVING, but derived table is more flexible
-- Top spending user from derived table
SELECT u.name, dt.total_spent
FROM (
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 1
) AS dt
JOIN users u ON u.id = dt.user_id;
-- Multi-level aggregation: avg of per-user totals
SELECT AVG(user_totals.total) AS avg_user_spend
FROM (
SELECT user_id, SUM(total_amount) AS total
FROM orders
GROUP BY user_id
) AS user_totals;Quick Quiz
Tip
Tip
Practice Subquery in FROM in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
CTEs for readability. Scalar for simple values. Avoid correlated subqueries (N+1 perf issue).
Common Mistake
Warning
A common mistake with Subquery in FROM is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Subquery in FROM from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A subquery in the FROM clause creates a temporary inline table (derived table).
- Must be aliased: FROM (SELECT ...) AS alias_name
- Acts like a real table - outer query can SELECT, WHERE, ORDER BY on its columns
- Non-correlated - runs once, result is reused