Master inline virtual relations in memory, mandatory table aliases, grain normalization, fan-out elimination, and multi-grain sibling derived table joins.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Today we master derived tables in the FROM clause: turning subqueries into on-the-fly virtual tables and defending against fan-out data corruption.
A structured four-pillar journey from inline virtual views to fan-out elimination and multi-grain synthesis.
Emphasize: Derived tables are the single most important architectural tool for preventing financial calculation bugs in multi-table queries.
In Session 24, we learned how to nest queries in WHERE and SELECT. Today we nest queries in the FROM clause.
Remind students of the 3 subquery slots introduced in Session 24: WHERE, SELECT, and FROM. Today we conquer the FROM slot.
sales.orders to sales.order_items and sales.payments in one query to show total items and total collected revenue together.”
SUM(DISTINCT payment) wiped out genuine duplicate payments of ₹500, corrupting legitimate customer transactions.
SELECT o.order_id, i.gross, p.paid FROM sales.orders o LEFT JOIN ( SELECT order_id, SUM(gross_amount) AS gross FROM sales.order_items GROUP BY order_id ) i ON o.order_id = i.order_id LEFT JOIN ( SELECT order_id, SUM(amount) AS paid FROM sales.payments GROUP BY order_id ) p ON o.order_id = p.order_id; -- Pre-aggregating guarantees 1:1 join!
Draw the multiplication matrix on the board: 4 items × 2 payments = 8 rows. Show how each payment amount gets duplicated 4 times.
How SQL engines parse inline derived tables and treat them as first-class relational sources.
(SELECT store_id, SUM(net_total)...)AS agg_ordersON s.store_id = agg_orders.store_idEmphasize the mental model: A derived table is simply a query whose output behaves as a table inside another query.
Click execution architectures below to observe how subqueries in FROM create temporary in-memory tables.
FROM clause that acts exactly like a temporary table for the duration of the query.FROM: FROM (...) AS sub. Omitting it throws a syntax error!Click Mode 2: Show how pre-aggregating 1,50,000 orders down to 200 store rows makes the final join instantaneous.
Embedding aggregated virtual tables in the FROM clause to decouple metric calculation from presentation.
FROM or JOIN clause, defining its operational scope.AS agg). Outer clauses reference inner columns via agg.total_revenue.stores.stores.SELECT
s.store_id,
s.store_name,
s.city,
agg.total_orders,
ROUND(agg.total_revenue, 2) AS total_revenue,
ROUND(agg.total_revenue / agg.total_orders, 2) AS avg_order_value
FROM stores.stores s
JOIN (
SELECT
store_id,
COUNT(*) AS total_orders,
SUM(net_total) AS total_revenue
FROM sales.orders
GROUP BY store_id
) AS agg ON s.store_id = agg.store_id
ORDER BY total_revenue DESC, s.store_id ASC
LIMIT 15;
Have students omit AS agg in playground to see PostgreSQL's famous error: subquery in FROM must have an alias.
Click join strategies below to see how derived tables eliminate Cartesian row multiplication across child tables.
Draw on the board: Order 1 has 3 items (₹100, ₹200, ₹300) and 2 payments (₹300, ₹300). Show how joining them produces 6 rows, and summing payment amount gives ₹1,800 instead of ₹600!
Engineering reliable financial reconciliation queries by isolating child table grains inside derived tables.
i aggregates items; derived table p aggregates payments. Neither interferes with the other.items_gross against total_amount_paid row-by-row without fear of inflated sums.LEFT JOIN preserves pending orders that have not yet recorded items or completed payments.SELECT
o.order_id,
o.order_date,
COALESCE(i.item_count, 0) AS total_items,
COALESCE(i.items_gross, 0) AS total_items_gross,
COALESCE(p.payment_count, 0) AS total_payments,
COALESCE(p.total_paid, 0) AS total_amount_paid
FROM sales.orders o
LEFT JOIN (
SELECT
order_id,
COUNT(*) AS item_count,
ROUND(SUM(gross_amount), 2) AS items_gross
FROM sales.order_items
GROUP BY order_id
) i ON o.order_id = i.order_id
LEFT JOIN (
SELECT
order_id,
COUNT(*) AS payment_count,
ROUND(SUM(amount), 2) AS total_paid
FROM sales.payments
GROUP BY order_id
) p ON o.order_id = p.order_id
ORDER BY o.order_id DESC
LIMIT 15;
Have students run this in playground: note how total_items and total_payments are accurate counts without any double counting.
Click execution modes below to observe how modular derived tables can be joined to each other as peers.
FROM clause, joined on a common dimension key.revenue / employee_count, with zero fear of cardinality distortion.Demonstrate how sibling derived tables allow calculating productivity metrics across two completely separate operational tables.
Synthesizing multiple independent operational domains at a shared regional dimension grain.
NULLIF(emp.employee_count, 0) to prevent catastrophic division-by-zero crashes.SELECT
rev.region_id,
rev.total_revenue,
rev.order_count,
emp.employee_count,
ROUND(rev.total_revenue / NULLIF(emp.employee_count, 0), 2) AS revenue_per_employee
FROM (
SELECT
s2.region_id,
ROUND(SUM(o.net_total), 2) AS total_revenue,
COUNT(DISTINCT o.order_id) AS order_count
FROM stores.stores s2
JOIN sales.orders o ON s2.store_id = o.store_id
GROUP BY s2.region_id
) rev
JOIN (
SELECT
s3.region_id,
COUNT(*) AS employee_count
FROM stores.stores s3
JOIN stores.employees e ON s3.store_id = e.store_id
GROUP BY s3.region_id
) emp ON rev.region_id = emp.region_id
ORDER BY rev.total_revenue DESC, rev.region_id ASC
LIMIT 15;
Point out NULLIF(emp.employee_count, 0): In SQL, division by zero halts the entire query. NULLIF turns 0 into NULL, which turns the quotient into NULL safely.
Comparing the inline nested syntax of derived tables against top-down Common Table Expressions.
WITH (Session 28). Can be referenced multiple times across the query.Tell students: If they master derived tables today, CTEs in Session 28 will feel natural and effortless.
Structuring clean, readable queries by encapsulating aggregation logic inside derived tables.
SELECT
b.brand_id,
b.brand_name,
agg.product_count,
ROUND(agg.avg_price, 2) AS avg_price,
ROUND(agg.max_price, 2) AS max_price
FROM core.dim_brand b
JOIN (
SELECT
brand_id,
COUNT(*) AS product_count,
AVG(price) AS avg_price,
MAX(price) AS max_price
FROM products.products
GROUP BY brand_id
) AS agg ON b.brand_id = agg.brand_id
WHERE agg.product_count >= 10
ORDER BY agg.avg_price DESC, b.brand_id ASC
LIMIT 15;
Demonstrate predicate pushdown: notice how the inner subquery groups products, but only qualifying brands appear in the final report.
Four frequent errors developers and data engineers encounter with derived tables in the FROM clause.
AS agg after the closing parenthesis in FROM, triggering an immediate syntax error.LATERAL keyword.revenue / employee_count without NULLIF(col, 0), causing runtime crashes on zero-staff regions.Emphasize Antipattern 01 (missing alias) and Antipattern 03 (fan-out). Both appear frequently in senior technical interviews.
Work through 4 graded challenges below. Test your queries live in the interactive terminal.
Give students 10 minutes to complete the drills. Focus on Challenge 3 (fan-out defense) and Challenge 4 (sibling derived tables).
Authentic technical questions asked by senior engineering and data analytics interview panels.
FROM clause must have an identifiable name in the relational catalog namespace. When you write an inline subquery, PostgreSQL generates a temporary anonymous relation. Without an explicit alias (e.g. AS agg), outer clauses (SELECT, JOIN, WHERE) have no identifier to qualify and resolve column references.
AS alias_name after a closing parenthesis in FROM. PostgreSQL halts immediately with error: 'subquery in FROM must have an alias'.
Have students practice explaining Question 2 (Join Fan-Out Defense). It is a classic senior interview question for analytics engineers.
Click cards to test your retention of derived table syntax, fan-out elimination, and multi-grain joins.
Relational Namespace! In SQL grammar, every relation in FROM must have an identifier to resolve column references.Cartesian explosion! Joining two 1-to-many child tables directly multiplies row counts and inflates aggregate sums.Grain Normalization! Collapsing child rows into one summary row per parent ensures a strict 1:1 join.Deduplication Bug! It destroys legitimate identical payments (e.g. two separate ₹500 transactions).NO! Standard derived tables evaluate independently. You must use a LATERAL join to reference outer columns.Readability and scope! Derived tables are nested inside-out; CTEs are declared top-down with WITH and are reusable.NO! The optimizer flattens simple derived tables into join trees or pushes WHERE predicates down via Predicate Pushdown.Peer Join! Joining two independent derived tables on a common dimension key in the FROM clause.Crash Prevention! Division by zero causes a fatal error; NULLIF converts 0 to NULL, yielding safe NULL output.Ambiguity or ugly names! Outer queries cannot reference raw aggregate expressions like COUNT(*) without an alias.Review card 2 (join fan-out) and card 5 (outer correlation limit). Both are essential for writing production queries.
Master these six rules to structure modular, fan-out immune queries in PostgreSQL.
FROM clause must have an assigned alias: FROM (...) AS alias_name. Never omit it.NULLIF(denominator, 0) to guard against fatal division-by-zero database crashes.Have students snapshot this slide: it is the comprehensive mental cheat sheet for derived tables and grain defense.
A syntax reference card for virtual tables, pre-aggregation patterns, and fan-out defense.
SELECT
s.store_id,
s.store_name,
s.city,
agg.order_count,
ROUND(agg.total_revenue, 2) AS total_revenue
FROM stores.stores s
JOIN (
SELECT
store_id,
COUNT(*) AS order_count,
SUM(net_total) AS total_revenue
FROM sales.orders
GROUP BY store_id
) AS agg ON s.store_id = agg.store_id
ORDER BY total_revenue DESC, s.store_id ASC
LIMIT 10;
FROM (SELECT ... GROUP BY ...) AS aggJOIN (SELECT ... ) AS agg ON key = agg.keyorders o LEFT JOIN (items) i LEFT JOIN (payments) pFROM (subquery1) s1 JOIN (subquery2) s2 ON keynumerator / NULLIF(denominator, 0)Bookmark this quick reference for practical syntax lookup during live coding and homework challenges.
Solidify your derived table mastery with these 4 production challenges across RetailMart schemas.
Pre-aggregate order counts and total sales in a derived table in FROM, then join to stores.stores to report store revenue and average order value.
SELECT
s.store_id,
s.store_name,
s.city,
agg.total_orders,
ROUND(agg.total_revenue, 2) AS total_revenue
FROM stores.stores s
JOIN (
SELECT
store_id,
COUNT(*) AS total_orders,
SUM(net_total) AS total_revenue
FROM sales.orders
GROUP BY store_id
) AS agg ON s.store_id = agg.store_id
ORDER BY total_revenue DESC, s.store_id ASC
LIMIT 10;
Use a derived table to pre-aggregate product metrics by brand_id, then join to core.dim_brand to report catalog depth and average pricing.
SELECT
b.brand_id,
b.brand_name,
agg.product_count,
ROUND(agg.avg_price, 2) AS avg_price
FROM core.dim_brand b
JOIN (
SELECT
brand_id,
COUNT(*) AS product_count,
AVG(price) AS avg_price
FROM products.products
GROUP BY brand_id
) AS agg ON b.brand_id = agg.brand_id
WHERE agg.product_count >= 10
ORDER BY agg.avg_price DESC, b.brand_id ASC
LIMIT 10;
Pre-aggregate order_items and payments in separate derived tables to compare items gross vs collected payments per order without multiplication.
Join sibling derived tables on region_id to compare total sales against total employee headcount with NULLIF division protection.
Assign these 4 challenges for student practice before the next live session on Correlated Subqueries & EXISTS.
You have mastered single-table queries, relational joins, set theory, and foundational derived tables. Here is your roadmap forward.
Celebrate with the class: they have mastered derived tables and are now ready for correlated row-by-row execution in the next class.
Moving beyond independent subqueries: executing inner queries dynamically for every outer row.
Send students off with excitement for Correlated Subqueries and EXISTS in Session 26.