Subqueries and CASE

This lesson covers scalar subquery, IN / EXISTS, correlated subquery, CASE WHEN, date arithmetic, GREATEST / LEAST. Read it, then solve questions 41–50 below it in the sidebar. Every query runs on a real PostgreSQL database inside your browser.

A subquery as a value

A SELECT in brackets that returns one value can be used wherever a number could: WHERE x > (SELECT AVG(x) FROM t). This is how you compare rows with an aggregate, which WHERE can’t do directly.

SELECT product_name, unit_price
FROM products
WHERE unit_price < (SELECT AVG(unit_price) FROM products);

A subquery as a list: IN and EXISTS

WHERE id IN (SELECT id FROM …) keeps rows whose id appears in the subquery’s result. EXISTS (SELECT 1 FROM … WHERE …) is true when the subquery returns any row. NOT IN breaks if the list contains a NULL; NOT EXISTS doesn’t.

SELECT customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM sales s
  WHERE s.customer_id = c.customer_id AND s.quantity_sold >= 5
);

Correlated subqueries

A subquery that refers to the outer row (like c.customer_id above) is re-evaluated for each row. Use it to compare a row with its own group, e.g. “products cheaper than their category’s average”.

SELECT category, product_name, unit_price
FROM products p
WHERE unit_price < (
  SELECT AVG(unit_price) FROM products p2 WHERE p2.category = p.category
);

Labels with CASE

CASE WHEN cond1 THEN a WHEN cond2 THEN b ELSE c END returns the value of the first true condition. Without ELSE, unmatched rows get NULL.

SELECT customer_name,
       CASE WHEN joined_on < '2024-01-01' THEN 'Old-timer'
            ELSE 'New'
       END AS member_type
FROM customers;

Date arithmetic

In PostgreSQL date - date is a whole number of days and date + 7 is a week later. EXTRACT(ISODOW FROM d) is 1 (Monday) … 7 (Sunday). MySQL spells these DATEDIFF(a, b) and DAYOFWEEK(d) (1 = Sunday).

SELECT customer_name,
       joined_on,
       DATE '2024-04-01' - joined_on AS days_as_member,
       TO_CHAR(joined_on, 'Day') AS weekday
FROM customers;

Largest of several values

MAX() works down a column. To pick the larger of several values in the same row, use GREATEST(a, b, …); LEAST gives the smallest.

SELECT sale_id, quantity_sold,
       GREATEST(quantity_sold, 3) AS at_least_three,
       LEAST(quantity_sold, 5) AS at_most_five
FROM sales
LIMIT 6;