Subqueries & CASE: Queries Inside Queries

A query as a value

Wrap a query in parentheses and it can be used wherever a value goes. A scalar subquery returns exactly one value, which is perfect for "above average" questions.

Example: Pizzas pricier than average

SELECT name, price FROM pizzas WHERE price > (SELECT AVG(price) FROM pizzas);

The inner query runs first, gives one number (about 13.7), and the outer WHERE uses it.

IN with a subquery

A subquery can also return a list, which IN is happy to consume. This is often an alternative to a JOIN.

Example: Customers who ordered a Gourmet pizza

SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE pizza_id IN (SELECT id FROM pizzas WHERE category = 'Gourmet'));

Read it inside out: gourmet pizza ids → orders with those pizzas → customer ids → names.

Subquery in FROM

You can also SELECT from the result of another query, as if it were a table. Give it an alias.

Example: Average of the per-customer totals

SELECT ROUND(AVG(total), 2) AS avg_pizzas_per_customer FROM (SELECT customer_id, SUM(quantity) AS total FROM orders GROUP BY customer_id) AS t;

The inner query builds a small table of totals; the outer query averages it.

CASE: if/else inside SQL

CASE WHEN condition THEN value ... ELSE value END turns data into labels. Great for bucketing numbers into categories.

Example: Label pizzas by price

SELECT name, price, CASE WHEN price < 11 THEN 'budget' WHEN price < 15 THEN 'mid' ELSE 'premium' END AS tier FROM pizzas;

Conditions are checked top to bottom; the first true one wins.

CASE inside aggregates

Sneaky trick: SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows matching a condition, per group, in one pass.

Example: Delivered vs not, per customer

SELECT customer_id, SUM(CASE WHEN delivered = 1 THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN delivered = 0 THEN 1 ELSE 0 END) AS pending FROM orders GROUP BY customer_id;

Two counts from one scan of the table.

Practice exercises

1. Above-average calories

   Show the name and calories of pizzas with more calories than the average pizza.

2. The most experienced chef's pizzas

   Show the name of every pizza made by the chef with the highest years_experience. Use a subquery, not a hard-coded id.

3. Unordered pizzas

   Show the name of every pizza that has never been ordered. Use NOT IN with a subquery.

4. Loyalty tiers

   Show each customer name and a tier column: 'gold' if loyalty_points >= 500, 'silver' if >= 200, otherwise 'bronze'.

5. Veggie share per category

   For each pizza category, count the vegetarian pizzas as veg and the non-vegetarian as meat, using CASE inside SUM.

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the Subqueries & CASE quiz: 5 timed questions.