Subqueries in FROM: derived tables

Part of the free LogicWiz SQL & Databases course, Module 6: Subqueries and set operations. The queries and practice run in the app on real Postgres.

The principal asks: "What is the average of the department averages?" That's an aggregate of an aggregate, and one GROUP BY can't do it. But a query's result is itself a table, so you can query that.

You'll learn to

  • Use a subquery in FROM as a temporary table
  • Aggregate an aggregate in two steps
  • Filter on a computed column or alias
  • Join a summary back to detail rows

Learn

A result is a table

Every SELECT returns a table. So you can put one in FROM, give it a name, and query it like any other table. This is a derived table (some say inline view).

It lets you work in steps: step 1 inside the brackets, step 2 outside. The outer query sees only the columns the inner one returns, with the names you gave them.

Departments with a high average

SELECT d.dept_id, d.avg_cgpa
FROM (SELECT dept_id, ROUND(AVG(cgpa), 2) AS avg_cgpa
      FROM students
      GROUP BY dept_id) AS d
WHERE d.avg_cgpa > 8
ORDER BY d.avg_cgpa DESC;

The inner query makes one row per department. The outer WHERE can use avg_cgpa, because to the outer query it is just a column. (HAVING would also work here; the next example needs the two steps.)

An average of averages

SELECT ROUND(AVG(avg_cgpa), 2) AS avg_of_dept_avgs,
       (SELECT ROUND(AVG(cgpa), 2) FROM students) AS overall_avg
FROM (SELECT dept_id, AVG(cgpa) AS avg_cgpa
      FROM students
      WHERE dept_id IS NOT NULL
      GROUP BY dept_id) AS d;

Two different numbers: the overall average weights every student equally; the average of averages weights every department equally, so small departments count as much as big ones. Which one is "right" depends on the question.

Three reasons to reach for one

  1. An aggregate of an aggregate: average of totals, max of counts.
  2. Filtering on something you computed: WHERE runs before SELECT, so it can't see an alias like percent. Wrap the query and filter outside.
  3. Summarise before joining: the cure for Module 5's fan-out. Total each table to one row per key, then join the totals.

Join a summary to the detail

SELECT s.name, s.cgpa, ROUND(d.avg_cgpa, 2) AS dept_avg
FROM students s
JOIN (SELECT dept_id, AVG(cgpa) AS avg_cgpa
      FROM students
      GROUP BY dept_id) AS d ON d.dept_id = s.dept_id
WHERE s.cgpa > d.avg_cgpa
ORDER BY s.name;

The same question as the correlated subquery in the last lesson, now as a join to a summary table. This version can also show the department average next to each student.

Give every derived table an alias (AS d) and every computed column a name (AS avg_cgpa). Postgres 16 and later accept a derived table with no alias, but MySQL and older Postgres reject it, and without a column name the outer query has to guess at avg. Name everything.

When the brackets start nesting three deep, switch to a CTE: WITH dept_avg AS (SELECT …) SELECT … FROM dept_avg. Same idea, but it reads top to bottom. Module 12 covers it.

Interview questions that are really derived tables: "department with the highest average salary", "customers whose total spend is above the average total spend", "average number of orders per customer". Each is: summarise in the brackets, then compare or aggregate outside.

In one line

A subquery in FROM (with an alias) is a temporary table: summarise inside, then filter, join or aggregate outside.