Subqueries in FROM — derived tables and their uses

Subqueries in FROM Create Virtual Tables

A subquery in the FROM clause produces a temporary virtual table — called a derived table — that the outer query can SELECT from, JOIN to, or filter with WHERE. The derived table must be given an alias.

-- Find categories where average order value exceeds the overall average
SELECT cat.category, cat.avg_value
FROM (
  SELECT category, AVG(amount) AS avg_value
  FROM orders
  GROUP BY category
) AS cat                           -- alias required
WHERE cat.avg_value > (
  SELECT AVG(amount) FROM orders
);

Joining a Derived Table

SELECT c.name, order_stats.total_orders, order_stats.total_spent
FROM customers c
JOIN (
  SELECT customer_id,
         COUNT(*)     AS total_orders,
         SUM(amount)  AS total_spent
  FROM orders
  GROUP BY customer_id
) AS order_stats ON c.id = order_stats.customer_id;

Derived Table vs CTE

Both express the same logic. A derived table is inline; a CTE is named at the top. When the same subquery is reused more than once in a query, a CTE avoids repetition. For a single use, either works — choose based on readability.