Skip to content

Joins on TPC-H

TPC logo

In this lab you will write outer joins, self joins, and set operations on the tpch database. TPC-H is a benchmark database for a wholesaler: customers place orders, each order has line items, and each line item is a part bought from a supplier. Keep the TPC-H Schema open while you work.

You will submit one file, joins.sql, with your query for each question. Answer each question in a -- comment next to the query it is about; those answers are what gets graded. Most questions tell you how many rows to expect, so you can check your own work. Steps 1–5 are required; Step 6 is if you have time.

Step 1: Set up

Start your SSH tunnel, open pgAdmin, and click the tpch database under your server. Then open the Query Tool on it. tpch is read-only, so nothing you run today can change it.

Download joins.sql and open it in the Query Tool (the folder icon, or Ctrl+O). It has a heading for every question; write each query under its heading. To run one query, highlight it and press F5. pgAdmin shows the number of rows under the result, which is how you check most answers today.

Note the following about the schema:

  • Every column has a prefix from its table, so the NAME column of part is p_name, and that of supplier is s_name. Two tables never share a column name, so you rarely need an alias like c.c_name.
  • lineitem has six million rows, and orders has one and a half million. Add LIMIT 10 while you work on a query, and remove it when you are done. If a query runs for more than about 10 seconds, cancel it (the stop button) and look for a missing join condition.

Write these warmup queries:

  1. Every nation with the name of its region, ordered by region and then nation. (25 rows)
  2. The name and phone number of every customer in CANADA whose account balance is over 9990. (9 rows) Compare the first two digits of each phone number with CANADA's n_nationkey. What do they tell you?

Step 2: Outer joins

  1. For customers 1 through 10, show c_custkey, c_name, and the number of orders each one placed. Use an inner JOIN. (7 rows) Then change it to a LEFT JOIN. (10 rows) Which customers have no orders, and what pattern do you see in their keys?
  2. Change count(o_orderkey) to count(*). Which rows change, and why?
  3. Every customer who has never placed an order: c_custkey and c_name. (50,004 rows) Which column did you test with IS NULL, and why that one?
  4. The number of customers with no orders, in each market segment (c_mktsegment). (5 rows; HOUSEHOLD has 10,161)
  5. Rewrite Question 1 with a RIGHT JOIN, so that it returns the same 10 rows. What did you have to change besides the keyword?

From the slides

Question 1 is on slide 5 and slide 6, Question 2 is slide 9, and Question 3 is slide 8.

Step 3: ON vs. WHERE

  1. For customers 1 through 10, the number of orders each one placed in 1998, including the customers who placed none. First put the date condition in the WHERE clause. (4 rows) Then move it into the ON clause. (10 rows; customers 7, 8, and 10 have 2 each) Explain why the first version lost six customers.
  2. For customers 1 through 10, the total quantity (l_quantity) of all the line items they ever ordered. Join customer, orders, and lineitem, starting with LEFT JOIN orders and then a plain JOIN lineitem. How many rows, and which customers are missing? Fix it so that all 10 appear. (customer 10 has 2012.00)
  3. In the fixed version, customers 3, 6, and 9 have a total of NULL. Wrap the sum in coalesce(sum(l_quantity), 0) to show 0 instead. Is 0 or NULL the better answer here?

From the slides

Question 1 is slide 10, and Question 2 is slide 11. coalesce(a, b) returns a, unless a is NULL, in which case it returns b.

Step 4: Self joins

  1. The nations in the same region as JAPAN, not including JAPAN itself. (4 rows) Use two aliases for nation, such as n1 for JAPAN and n2 for the others.
  2. Every pair of nations in the same region, with the region's name, listing each pair only once. (50 rows) Before you run it, predict how many rows you would get with <> instead of <, and with no condition on the keys at all. Then check your predictions.

From the slides

Question 2 is the query on slide 12.

Step 5: Set operations

  1. One contact list of the customers in CANADA with an account balance over 9990, and the suppliers in CANADA with an account balance over 9900. Show the name, the phone number, and a third column that says customer or supplier, ordered by name. (12 rows)
  2. The customers who have never placed an order, using EXCEPT instead of a join. (50,004 rows, as in Step 2) Then add c_name to the first SELECT. What error do you get, and why?
  3. The customers (o_custkey) who placed an order in 1997 and in 1998, using INTERSECT. (62,637 rows) Then change it to EXCEPT, UNION, and UNION ALL, and write down the number of rows for each. Why is UNION ALL so much bigger than UNION?
  4. 86,680 customers ordered in 1997, and 71,057 ordered in 1998. Use those two numbers and your INTERSECT count to explain the UNION count. Then swap the two queries around the EXCEPT. Do you get the same answer?

For the dates, either o_orderdate BETWEEN '1997-01-01' AND '1997-12-31' or extract(year FROM o_orderdate) = 1997 works.

From the slides

Question 1 is the query on slide 13, Question 2 is slide 15, and Question 3 is the table on slide 14.

Step 6: Harder "none" questions (if time)

The suppliers in GERMANY who supply no part of size 50. There are 396 suppliers in GERMANY.

  1. Write it with EXCEPT: the German suppliers' keys, except the keys of suppliers who supply a part of size 50. (84 rows)
  2. Now try it with outer joins, all in one FROM clause: 
    FROM supplier
  JOIN nation ON n_nationkey = s_nationkey
  LEFT JOIN partsupp ON ps_suppkey = s_suppkey
  LEFT JOIN part ON p_partkey = ps_partkey AND p_size = 50
WHERE n_name = 'GERMANY' AND p_partkey IS NULL
    This returns 31,099 rows, not 84. What do those rows have in common?
  3. Fix it by joining partsupp and part together first, and then outer-joining the result: 
      LEFT JOIN (partsupp JOIN part ON p_partkey = ps_partkey AND p_size = 50)
    ON ps_suppkey = s_suppkey
    Which version, EXCEPT or the join, would you rather read? Which one lets you show s_name too?

On Tuesday, we'll write this a third way, with a subquery.

Submission

Submit your file joins.sql via Gradescope by the end of the day. The autograder only checks that the file has the queries and comments the steps ask for; it does not run your queries. Your answers to the questions are graded by hand.