SQL Derived Tables: Subqueries in FROM and Preaggregation

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

Place a SELECT in parentheses in FROM and give it a table alias. The outer query can refer to its selected column names, including useful expression aliases such as known_total. It cannot normally reach source fields the inner SELECT did not expose. Think of the inner output as an interface: its rows and columns are the contract available to the next query level.

Preaggregation is a common use. SELECT customer_id,SUM(amount) AS known_total FROM sales GROUP BY customer_id produces one row per customer link. Joining that result to customers avoids repeating one customer total for every sale when the outer report needs customer-level rows. If two separate one-to-many inputs are needed, aggregate each at the intended joining grain before combining them to avoid a many-by-many multiplication.

A derived table also establishes a new scope for selected aliases. An inner calculation amount*2 AS doubled_amount can be filtered by the outer WHERE doubled_amount>40. This is useful because ordinary same-level WHERE cannot portably refer to a newly selected alias. The outer filter sees actual output columns from the inner query rather than relying on engine-specific alias extensions.

A derived table is not automatically a stored temporary table. The optimiser may inline, merge or materialise it according to the query and engine. Do not infer physical execution order solely from the written nesting. An inner ORDER BY also does not generally promise final output order unless it participates in an appropriate limiting operation; write a final outer ORDER BY. Ordinary derived tables are not universally allowed to reference prior FROM items without lateral or apply features, whose syntax and availability differ. Keep simple derived inputs independent unless the engine-specific correlation mechanism is intentional.

The parenthesised SELECT with an alias is supported by all four systems. Some newer engines relax alias requirements, but writing the alias is the portable habit. LATERAL and SQL Server APPLY are related tools for correlated FROM inputs and are not the same universal syntax as this independent derived table.

Check the engine-specific syntax and version requirements in PostgreSQL subqueries in FROM.

Use a derived table to calculate doubled amounts, then return only sales whose doubled amount exceeds forty.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,doubled_amount FROM (
 SELECT id,amount*2 AS doubled_amount FROM sales
) AS doubled WHERE doubled_amount>40 ORDER BY id;

id | doubled_amount
102 | 60

The inner query exposes id and doubled_amount. The outer query can filter that alias because it is now a column of its input. Only thirty doubled to sixty exceeds forty; twenty doubled to forty meets equality but not the strict greater-than condition.

Run the inner customer-total query independently and count its summary rows, including the anonymous link group. Then run the outer customer join and confirm its four identified customer rows. Remove known_total from the inner projection and observe that the outer reference becomes invalid. The scope boundary exposes only selected columns, even though the source amount still exists.