Outer Joins, Set Operations

CS 374, Fall 2026

Today: Back to SQL

• Oct 06 -- Oct 20: SQL from Python, through SQLModel
• Oct 22 -- Oct 29: queries that are hard to write any way
  • Today: outer joins and set operations
  • Oct 27: subqueries
  • Oct 29: window functions
• Nov 05: the same queries from Python

New database: tpch on data.cs.jmu.edu, in pgAdmin

TPC-H: A Benchmark Database

• The Transaction Processing Performance Council publishes database benchmarks
• TPC-H is "decision support": a wholesaler with customers and suppliers
• Vendors run the same 22 queries on it to compare their systems

Reading the Schema

region   (r_regionkey, r_name, r_comment)
nation   (n_nationkey, n_name, n_regionkey, ...)
customer (c_custkey, c_name, c_nationkey, c_acctbal, c_mktsegment, ...)
orders   (o_orderkey, o_custkey, o_orderdate, o_totalprice, ...)
lineitem (l_orderkey, l_linenumber, l_partkey, l_suppkey, l_quantity, ...)

• Every column has a prefix from its table: c_name, s_name, p_name
• So two tables never share a column name, and you rarely need c.c_name
• Foreign keys are named for what they point to: o_custkey → c_custkey
• lineitem is 1 GB: add LIMIT 10 while you work, and filter early

Inner Joins Drop Rows

SELECT c_custkey, c_name, count(o_orderkey)
FROM customer
  JOIN orders ON o_custkey = c_custkey
WHERE c_custkey <= 10
GROUP BY c_custkey, c_name
ORDER BY c_custkey;

• Ten customers go in; seven rows come out
• Customers 3, 6, and 9 have no orders, so no row of orders matches them
• An inner join keeps only the pairs that satisfy ON

Question: how would you list the customers who have no orders?

LEFT JOIN Keeps the Left Table

SELECT c_custkey, o_orderkey, o_orderdate
FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
WHERE c_custkey <= 10
ORDER BY c_custkey, o_orderdate;

• Every left row appears; if nothing matched, the right columns are NULL
• That NULL means "no match," not "unknown order date"

Predict: Four Kinds of Join

Table a has x = 1, 2, 3. Table b has x = 2, 3, 4.
SELECT a.x, b.x FROM a ___ JOIN b ON a.x = b.x;

• a RIGHT JOIN b is b LEFT JOIN a; most people only write LEFT
• In TPC-H, customer RIGHT JOIN orders adds nothing. Why?

Finding What Is Missing

SELECT c_custkey, c_name
FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
WHERE o_orderkey IS NULL;

• Keep every customer, then keep only the ones with no match
• Test a column that can't be NULL in a real match: the primary key is safest
• 50,004 of the 150,000 customers have never ordered

This pattern answers every "which X have no Y?" question:
courses nobody took, students with no advisor, rooms never booked

count(*) vs. count(column)

SELECT c_custkey, count(*), count(o_orderkey)
FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
WHERE c_custkey <= 10
GROUP BY c_custkey;

• count(*) counts rows, and customer 3 still has one row, full of NULLs
• count(col) counts values that are not NULL
• After an outer join, count a column from the right table

ON vs. WHERE

-- Orders in 1998, per customer, including customers with none
SELECT c_custkey, count(o_orderkey)
FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
WHERE c_custkey <= 10 AND o_orderdate >= '1998-01-01'
GROUP BY c_custkey;

• This returns 4 rows, not 10. The zeros are gone. Why?
• WHERE runs after the join: NULL >= '1998-01-01' is not true
• A WHERE condition on the right table turns a LEFT JOIN into an inner join

Fix: move the condition into ON, so it decides what matches

  LEFT JOIN orders ON o_custkey = c_custkey AND o_orderdate >= '1998-01-01'

Chains of Joins

SELECT c_custkey, count(l_linenumber), sum(l_quantity)
FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
  JOIN lineitem ON l_orderkey = o_orderkey      -- drops 3, 6, and 9 again
WHERE c_custkey <= 10
GROUP BY c_custkey;

• Joins run left to right; each one sees the result of the one before
• Customer 3's row has o_orderkey = NULL, which matches no line item
• Once a table is outer-joined, every join after it on its rows must be outer too

  LEFT JOIN lineitem ON l_orderkey = o_orderkey

Self Joins

-- Every pair of nations in the same region
SELECT r_name, n1.n_name, n2.n_name
FROM nation AS n1
  JOIN nation AS n2 ON n2.n_regionkey = n1.n_regionkey
  JOIN region ON r_regionkey = n1.n_regionkey
WHERE n1.n_nationkey < n2.n_nationkey
ORDER BY r_name, n1.n_name, n2.n_name;

• Two aliases make two copies of one table; you must use them everywhere
• 5 regions of 5 nations: how many rows with <? With <>? With neither?
• < keeps each pair once, and drops each nation paired with itself

Set Operations

SELECT c_name AS name, c_phone AS phone, 'customer' AS kind
FROM customer JOIN nation ON n_nationkey = c_nationkey
WHERE n_name = 'CANADA' AND c_acctbal > 9990
UNION
SELECT s_name, s_phone, 'supplier'
FROM supplier JOIN nation ON n_nationkey = s_nationkey
WHERE n_name = 'CANADA' AND s_acctbal > 9900
ORDER BY name;

• Each SELECT must have the same number of columns, of compatible types
• Column names come from the first SELECT
• One ORDER BY, at the very end, for the whole result

UNION, INTERSECT, EXCEPT

• 86,680 customers ordered in 1997, and 71,057 in 1998
• All but UNION ALL remove duplicates, like SELECT DISTINCT
• UNION ALL is faster: no sorting to find duplicates

Two Ways to Ask "Which Have None?"

SELECT c_custkey FROM customer
EXCEPT
SELECT o_custkey FROM orders;

SELECT c_custkey, c_name FROM customer
  LEFT JOIN orders ON o_custkey = c_custkey
WHERE o_orderkey IS NULL;

• Same 50,004 customers either way
• EXCEPT compares whole rows, so both sides need the same columns
• To show c_name too, the join is easier (or a subquery: Oct 27)

Where This Is Going

• GP3: Build and Load -- due Monday, Oct 26
• PE1: Peer Evaluation -- due Tuesday, Oct 27
• Oct 27: Subqueries, more TPC-H, and the Exam 1 retake
• Oct 29: Window Functions
• HW5: Analytical Queries -- due Monday, Nov 02

Lab: Joins on TPC-H

Outer joins, self joins, and set operations, in pgAdmin