Working with Data

Knowledge check 2 — 10 minutes, closed book, 3 questions

• Covers Session 1 and Homework 1: the key test, COUNT, NULL in a WHERE, GROUP BY, ORDER BY and LIMIT
• Laptops closed, phones away. On the desk: the paper, a pen, and your one handwritten A4 sheet.
• One scenario, three questions. Each question stands on its own.
• Worth 5% of the final grade
• This block's lecture is 35 minutes, not 45: the check comes out of the lecture, never out of the lab
• Hand it in when the timer goes. Then I say the three answers, once.

Knowledge check 2 — the three answers, said once

Listen, and mark your own memory of what you wrote.

The written key is on Moodle next Monday, with the marking by clause. Your marked paper is at the TA office from the same day.

This block in one sentence

A join puts columns from two tables in one row.

After every join, two questions:

1. What is one row now?
2. Did every row find its partner?

What we cover

1. Why the data is in several tables, and how they point at each other
2. The first join, and the check that goes with every join
3. Join, then GROUP BY. A third table.
4. JOIN and LEFT JOIN: the rows that have no partner
5. Lab 3, in teams of three

Why the data is in several tables

An order's row does not hold its customer's state: it holds a customer_id, and customers has a row with the same customer_id. The shared column is how one table points at another.

The first join, in a picture

Three real orders. Each one points at one customer. Three rows in, three rows out.

In the query, the tables get short names: orders AS o, customers AS c. Then every column says which table it comes from: o.order_id, c.customer_state.

The first join, as a query

Watch (cell 1 of lecture.ipynb): every order, with its customer's state.

FROM one table, JOIN the other, ON the columns that must be equal.

Predict, then watch: how many rows did the join return?

Cell 2:

con.sql("""
    SELECT COUNT(*)                   AS rows_in_result,
           COUNT(DISTINCT o.order_id) AS distinct_orders_in_result
    FROM orders AS o
    JOIN customers AS c ON c.customer_id = o.customer_id
""").df()

Predict: orders has 2,653 rows (SELECT COUNT(*) FROM orders, Session 1), and customers has 2,653 rows. What are the two numbers?

How many rows did the join return? — what it printed

2,653 in · 2,653 rows out · 2,653 distinct orders out. With JOIN, three equal counts mean every order found exactly one customer. Nothing was left out, and nothing was repeated. One row is still one order.

The three counts go with every join: the rows that went in, the rows that came out, the distinct keys that came out. This pair of tables is the easy case, one customer per order, so the three agree. They are here for the joins where they do not, in a few slides.

Join, then GROUP BY: orders per customer state

Watch (cell 3). Every clause from Session 1 works in a query that has a join in it: here GROUP BY and ORDER BY.

A third table is one more JOIN line

Watch (cell 4): item sales by customer state. The price is on order_items; the state is on customers; orders connects them: order_id from items to orders, customer_id from orders to customers.

price is one item's amount: order_items has no quantity column, so SUM(i.price) is the total. Last week it was Quantity * Price.

The same check, for three tables

Watch (cell 5). An item is identified by two columns, so the distinct count takes the pair.

3,024 items in · 3,024 rows out · 3,024 distinct items out. Every item kept, exactly once. One row is one item.

A join where some rows have no partner

The same three orders, joined to their items. One has one item. One has three. One has none.

Predict, then watch: every order with its items

Cell 6:

con.sql("""
    SELECT COUNT(*)                   AS rows_in_result,
           COUNT(DISTINCT o.order_id) AS distinct_orders_in_result
    FROM orders AS o
    JOIN order_items AS i ON i.order_id = o.order_id
""").df()

Predict: orders has 2,653 rows. Will the result have more rows than that, or fewer? And will all 2,653 orders be in it?

Every order with its items — what it printed

2,653 in · 3,024 rows out · 2,610 distinct orders out.

More rows: 3,024. One row is now one item, and order_items has 3,024 rows.

Fewer orders: 2,610. 43 orders have no items. JOIN keeps only the rows that found a partner, and says nothing about the rest.

LEFT JOIN keeps every row of the left table

FROM orders AS o LEFT JOIN order_items AS i: every order stays. Where an order has no partner, the item columns are NULL.

Predict, then watch: the same query, with LEFT JOIN instead of JOIN

Cell 7: one word added.

con.sql("""
    SELECT COUNT(*)                   AS rows_in_result,
           COUNT(DISTINCT o.order_id) AS distinct_orders_in_result
    FROM orders AS o
    LEFT JOIN order_items AS i ON i.order_id = o.order_id
""").df()

Predict: with JOIN: 2,653 orders in, 3,024 rows out, 2,610 distinct orders out. What are the two numbers now?

The same query, with LEFT JOIN — what it printed

2,653 in · 3,067 rows out · 2,653 distinct orders out. 3,067 is 3,024 and 43 more: one row for each order that has no items. Every row of the left table, orders, is there at least once; where it has no partner, the columns from order_items are NULL.

What LEFT JOIN kept

Watch (cell 8): the three orders of the picture, in the result of the LEFT JOIN.

<NA>, NaN: pandas's NULL. left(…, 8): a text function, not LEFT JOIN.

The rows with no partner

Watch (cell 9): LEFT JOIN, then keep the rows where the right table's key IS NULL: the column from the other table is NULL exactly when there was no partner.

The rows with no partner: what are they?

Watch (cell 10): the same join and the same rows. What changes is the question: which orders are they? Grouped by the order's status.

A NULL key finds no partner

Watch (cell 11): cell 8's five rows, with one more condition: AND i.order_item_id = i.order_item_id. A value is always equal to itself, so every real row passes. A NULL is not a value: NULL = NULL is not true, so that row fails.

Which join does the question need?

Put the table whose rows you must keep after FROM, then LEFT JOIN the other one.

The names you will meet elsewhere: JOIN is an inner join (INNER JOIN); LEFT JOIN is an outer join (LEFT OUTER JOIN). RIGHT JOIN keeps every row of the right table, FULL JOIN every row of both: you will read them, and rarely need them.

What you just saw, as one idea

A join runs, and prints a table, whether or not every row found a partner.

• orders joined to customers: 2,653 in · 2,653 rows out · 2,653 distinct orders out. Every row found one partner.
• orders joined to order_items: 2,653 in · 3,024 rows out · 2,610 distinct orders out. One row became one item, and 43 orders are not there.

Nothing in the result says which of these happened. The three counts do, and so does a total computed without the join.

From today, labs are done in teams of three

One team for the whole day. One work laptop. Three roles:

In the second lab you take a role you have not had today. The homework, the knowledge check and the exam are yours alone.

5-minute stand-and-stretch — start the Lab 3 clone first

The keyboard person starts the clone and uv sync. Then everyone takes the break: it downloads while you are away.

In your terminal (Git Bash on Windows, Terminal on macOS), in the folder where you keep course work:

git clone https://github.com/earino/ecbs5294-lab03-joins.git
cd ecbs5294-lab03-joins
uv sync

Then leave the laptop. Back in five.

Lab 3 — The category table that came out short

Where this lab goes: you write your own join, and find out what this marketplace sells most. Then, in section D, a colleague's table of item sales by product category that adds up to 351,299.08 BRL, where total item sales are 364,110.54 BRL. Nothing errors.

First minute: the clone finished? The keyboard opens the folder in VS Code, opens notebooks/report.ipynb, picks the .venv kernel, types your names in the Your team cell, and runs the three setup cells. Then top to bottom, running each cell as you come to it.

Your work, in report.ipynb. A: look at the two tables, and name the column they share. B: your first join, from an empty cell, with its check. C: what sells most? Guess, then write it. D: your colleague's report: count what its join kept, find the items that are missing, then keep them, and finish the one comment: why the table came out short. E: the check, supplied: five lines, PASS or FAIL.

The syntax is on one page: REFERENCE-sql.md. The team next to you at minute 33. The checkpoint is the notebook, on Moodle.

The last ten minutes — explain it to the team next to you

Two teams, one laptop at a time. At your laptop, finished or not, start with one result you found interesting; then the last step you reached; if you repaired the report, why it was wrong and what the check printed. About two minutes.

The other team asks one question. If you reached the report: why was it wrong, not just where? If not: what is one row of what you showed? Then swap laptops.

Then the what-if, both teams together: a teammate writes the repair with the two tables the other way round: FROM product_category_name_translation AS t LEFT JOIN order_items_w_cat. Does that table add up to the items total? Which rows does it keep? Commit to an answer before anyone runs it. Unsure, or the teams disagree? Hands up.

The lab ends here, finished or not. Before you leave, the keyboard uploads the notebook as it is to Moodle; everyone else pastes the Your team line.