Derived Tables: Subqueries in FROM

This lesson covers derived tables — using a subquery inside the FROM clause — so you can filter an already-aggregated result further. It's for anyone searching "SQL derived table tutorial."

Writing FROM (SELECT ...) uses a SELECT's result as a temporary table on the spot. This is called a derived table, and it's handy when you need to filter an aggregated result even further.

The example first aggregates a per-class headcount as a derived table named class_counts, then filters that with WHERE count >= 3 from the outside. Think of it as a two-step flow: aggregate first, then filter the aggregate.

A common early mistake is not seeing how this differs from a HAVING clause — this pattern expresses "aggregate, then filter the result further" as a single query, in a way that plain HAVING can't always capture on its own, giving you the flexibility to handle more complex conditions. Once derived tables feel natural, you can chain multiple aggregation steps together to build genuinely sophisticated analytical queries.

CREATE TABLE students (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO students VALUES (1,'Alice',1),(2,'Bob',1),(3,'Charlie',2),(4,'Diana',2),(5,'Eric',2);

SELECT * FROM (
  SELECT class_id, COUNT(*) AS count
  FROM students
  GROUP BY class_id
) AS class_counts
WHERE count >= 3;

OUTPUT

💡 The SQL engine may take a few seconds to load the first time you run code.