Derived Tables & CASE Expressions

A subquery in the FROM clause

A derived table is a subquery placed in the FROM clause and given an alias — it becomes a temporary, named virtual table the outer query can join and select from like any real table. It solves a problem that neither a WHERE-subquery nor a plain join can: showing an individual row's value next to a set-level aggregate.

A WHERE-clause subquery can't *display* its aggregate (it only filters), and a plain join can't mix an aggregate with non-aggregated columns without forcing a GROUP BY. The derived table sidesteps both: compute the aggregate as a one-row table, then join the detail rows to it.

Worked example: products priced above the overall average (exam 6-14)

*Question:* show each product that costs more than the overall average price — and display that average alongside.

SELECT p.name, p.price, a.avg_price
FROM   products p
CROSS JOIN (SELECT AVG(price) AS avg_price FROM products) AS a   -- ← derived table
WHERE  p.price > a.avg_price
ORDER BY p.price DESC;

The derived table a is a single row holding the overall average (23.83). CROSS JOIN attaches that one value to every product row, so the WHERE can compare each price to it and the SELECT can display it. Result: Headphones 59, Hoodie 45, Laptop Stand 39.99, Lab Manual 24.50 — each shown with the average. A bare WHERE price > (SELECT AVG(price) …) would filter correctly but couldn't *show* the average in the output; the derived table can.

CASE: if-then-else inside SQL

CASE brings conditional logic into the SELECT list (or WHERE/ORDER BY). The searched form tests conditions top-to-bottom and returns the first match's value:

SELECT name, price,
  CASE WHEN price >= 40 THEN 'premium'
       WHEN price >= 18 THEN 'standard'
       ELSE 'budget'
  END AS tier
FROM products
ORDER BY price DESC;

Each product gets a label from its price band. Order matters: a $45 product matches the first WHEN and stops, so it's 'premium', never 'standard'. With no ELSE, unmatched rows return NULL.