Subqueries

CS 374, Fall 2026

Today

• One loose end from Thursday: coalesce
• Subqueries in WHERE, SELECT, and FROM
• When to write a subquery, and when to write a join
• Lab: Subqueries on TPC-H (20 min)
• Exam 1 Retake: the last 30 minutes, 1:30 to 2:00

coalesce: A Default for NULL

SELECT c_custkey, sum(l_quantity), coalesce(sum(l_quantity), 0)
FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
  LEFT JOIN lineitem ON l_orderkey = o_orderkey
WHERE c_custkey <= 6
GROUP BY c_custkey;

• coalesce(a, b, ...) returns the first argument that is not NULL
• sum of no rows is NULL, but count of no rows is 0

What Is a Subquery?

• A SELECT inside another statement, in parentheses
• The inner query runs first (in principle), and the outer query uses its result

A Single Value in WHERE

-- Customers whose balance is above average
SELECT c_custkey, c_name, c_acctbal
FROM customer
WHERE c_acctbal > (SELECT avg(c_acctbal) FROM customer);

• The subquery returns one row and one column: 4495.51
• 74,766 rows. You could type 4495.51 instead, but then the query is wrong as soon as the data changes
• Why not WHERE c_acctbal > avg(c_acctbal)?
  • WHERE filters rows before any grouping, so there is nothing to average yet

A List in WHERE: IN and NOT IN

-- Suppliers in GERMANY that supply no part of size 50
SELECT s_suppkey, s_name
FROM supplier
  JOIN nation ON n_nationkey = s_nationkey
WHERE n_name = 'GERMANY'
  AND s_suppkey NOT IN (
    SELECT ps_suppkey
    FROM partsupp
      JOIN part ON p_partkey = ps_partkey
    WHERE p_size = 50
  );

• The same 84 suppliers as Thursday's EXCEPT, but now s_name comes along for free
• The subquery returns one column and many rows

The NOT IN Trap

SELECT x FROM (VALUES (1), (2), (3)) AS a(x)
WHERE x NOT IN (2, NULL);

• Zero rows! Is 1 equal to NULL? Unknown, so 1 NOT IN (2, NULL) is not true
• One NULL anywhere in the list, and NOT IN returns nothing
• It is also slow: PostgreSQL can't use a hash table, so it compares every pair
• IN doesn't have this problem, only NOT IN

Remember: use NOT IN only on a column that can't be NULL, like a primary key; otherwise use NOT EXISTS

EXISTS and Correlated Subqueries

-- Customers who have never placed an order
SELECT c_custkey, c_name
FROM customer
WHERE NOT EXISTS (
  SELECT *
  FROM orders
  WHERE o_custkey = c_custkey
);

• c_custkey comes from the outer query: the subquery is correlated
• Read it as "for each customer, is there an order with this customer's key?"
• EXISTS only asks whether a row exists, so SELECT * is fine
• 50,004 rows, the same as the LEFT JOIN ... IS NULL from Thursday

A Correlated Value

-- The biggest line item(s) in each order of customer 10
SELECT o_orderkey, li.l_linenumber, li.l_quantity
FROM orders
  JOIN lineitem AS li ON li.l_orderkey = o_orderkey
WHERE o_custkey = 10
  AND li.l_quantity = (
    SELECT max(l_quantity)
    FROM lineitem
    WHERE l_orderkey = li.l_orderkey
  );

• Both queries use lineitem, so the outer one needs an alias, as in a self join
• 21 rows for 20 orders: order 3942656 has two line items of 50

A Subquery in SELECT

SELECT n_name,
  (SELECT count(*) FROM supplier WHERE s_nationkey = n_nationkey) AS suppliers,
  (SELECT count(*) FROM customer WHERE c_nationkey = n_nationkey) AS customers
FROM nation
ORDER BY n_name;

• Runs once per row of the outer query: fine for 25 nations
• Joining both supplier and customer would pair every supplier with every customer
  • ALGERIA would have 420 × 5,925 rows before grouping
• Careful: once per row of orders is 1.5 million times

A Table in FROM

SELECT avg(count(*)) FROM orders GROUP BY o_custkey;
-- ERROR:  aggregate function calls cannot be nested

-- Average number of orders per customer
SELECT avg(num_orders)
FROM (
  SELECT o_custkey, count(*) AS num_orders
  FROM orders
  GROUP BY o_custkey
) AS per_customer;

• Aggregate first, then aggregate the result: two levels, two queries
• Name every computed column (AS num_orders), so the outer query can use it
• 15.0 orders per customer. True or false?

Which Customers Count?

SELECT avg(num_orders)
FROM (
  SELECT c_custkey, count(o_orderkey) AS num_orders
  FROM customer
    LEFT JOIN orders ON o_custkey = c_custkey
  GROUP BY c_custkey
) AS per_customer;

• 10.0: the 50,004 customers with no orders now count as 0
• Neither answer is wrong; they answer different questions
• An outer join inside a subquery works like any other

Subquery or Join?

SELECT count(*) FROM customer                  -- 23,103
WHERE c_custkey IN (
  SELECT o_custkey FROM orders
  WHERE o_orderpriority = '1-URGENT' AND o_orderdate >= '1998-01-01'
);

SELECT count(*) FROM customer                  -- 26,652!
  JOIN orders ON o_custkey = c_custkey
WHERE o_orderpriority = '1-URGENT' AND o_orderdate >= '1998-01-01';

• IN asks whether a customer matches; JOIN makes a row for each match
• Fix the join with count(DISTINCT c_custkey), or SELECT DISTINCT

When Is Each One Clearer?

• Correlated subqueries are easy to read but can be slow
  • The richest supplier in each nation: 3.4 sec correlated, 0.09 sec as a join
• PostgreSQL rewrites many subqueries as joins for you, but not all of them

Where This Is Going

• PE1: Peer Evaluation -- due tonight
• GP3 reviews: comments due before Thursday's class
• Week 10 reading -- due Wednesday
• Oct 29: Window Functions
• HW5: Analytical Queries -- due Monday, Nov 02
  • Queries 1--6 use only what we have covered so far

Lab: Subqueries on TPC-H

20 minutes, then the Exam 1 Retake