Join tables

What you will be able to do

Combine the rows of two tables, such as each ticket with its customer’s segment. Then prove that the join did not lose rows or repeat them. A join that adds or drops rows gives no error, and every total that you calculate after it is then wrong.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions. Its first cell downloads the practice data and builds the database, so it works without the local project from Module 1. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• GROUP BY, COUNT(DISTINCT …) and CASE, from Aggregate correctly.
• The keys that connect the tables, from Relational concepts: tickets.customer_id points to customers.customer_id, and outcomes.ticket_id points to tickets.ticket_id. One ticket can have many outcome events.

What you need on your computer (only for the local route; the notebook and the boxes on this page need none of it)

• The folder ~/projects/ticket-data with larkfield.db and q.py. You made it in Set up a practice database: Build the database.
• If the folder or the database is missing or changed, follow that section again, or reset the database with python3 get_data.py --reset.
• A terminal open in that folder. Type python3 on macOS and Linux and python on Windows, as explained in Which Python command to type.
• The saved queries of this module go in queries/, numbered from 21-, as in Select and filter. This lesson adds 24- to 27-.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4). The browser boxes use SQLite 3.49.1. The notebook was tested in Jupyter.

Four tickets and five events

A join combines two tables into one result. For each row of the first table, it finds the rows of the second table that match a condition. It puts each matching pair side by side in one row. The condition comes after ON. It usually says that a foreign key equals a primary key: ON o.ticket_id = t.ticket_id.

Here are two tiny tables. The data is the same as in the boxes and the explorer below.

tickets (the left table, 4 rows):

ticket_id	customer_id	team
T-1	C-1	delivery
T-2	C-1	returns
T-3	C-2	payment
T-4	NULL	account

outcomes (the right table, 5 rows):

outcome_id	ticket_id	status
O-1	T-1	escalated
O-2	T-1	resolved
O-3	T-2	resolved
O-4	T-3	resolved
O-5	T-9	resolved

Three things in these tables matter for a join. T-1 has two events. T-4 has no event. And O-5 belongs to T-9, a ticket that is not in tickets: an orphan, a row whose key points to nothing.

There are two common joins:

• An inner join (INNER JOIN, or only JOIN) keeps only the pairs that match. A row with no match is not in the result.
• A left join (LEFT JOIN) keeps every row of the left table. A left row with no match gets one result row, with NULL in each column of the right table.

Here are both results, with what happened to each row:

ticket_id	team	outcome_id	status	INNER JOIN	LEFT JOIN
T-1	delivery	O-1	escalated	kept, repeated (T-1 matched 2 events)	kept, repeated
T-1	delivery	O-2	resolved	kept, repeated	kept, repeated
T-2	returns	O-3	resolved	kept	kept
T-3	payment	O-4	resolved	kept	kept
T-4	account	NULL	NULL	dropped (no event)	kept, NULLs added
Rows				4	5

O-5 is in neither result: no ticket matches T-9. A left join keeps the unmatched rows of the left table only.

The row counts tell the story. The left table has 4 rows, and the inner join also returns 4. That looks safe, but it is not: T-4 is lost and T-1 is there twice. The two changes hide each other. So count the distinct tickets as well: the inner join has 3, the left join has 4.

Run SQL in your browser

Try it: the tiny join

This box has only the tiny tables above (and three customers, C-1 to C-3). Run the query: you should see 4 rows, with T-1 twice and no T-4. Then change INNER JOIN to LEFT JOIN and run it again: 5 rows, and T-4 has NULL for outcome_id and status.

SQL query (you can change it)SELECT t.ticket_id, t.team, o.outcome_id, o.status FROM tickets AS t INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id ORDER BY t.ticket_id, o.outcome_id;

tickets AS t gives the table a short name, an alias, for this query. t.ticket_id then means “the ticket_id column of tickets”. Both tables have a ticket_id column, so each column name needs its table.

Explore

Which rows does a join keep and repeat?

SELECT t.ticket_id, o.outcome_id, o.status
FROM tickets AS t
INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id;

tickets (left table)

4 rows

outcomes (right table)

5 rows

Result

4 rows

Distinct tickets in the result

3

INNER JOIN: 4 ticket rows in, 4 rows out, for 3 tickets. Repeated: T-1 (2 times), once for each matching event. Dropped: T-4, because it has no event. O-5 is never in the result: its ticket, T-9, is not in tickets.

tickets (left table): 4 rows

ticket_id	team	in the result
T-1	delivery	→ 2 rows
T-2	returns	→ 1 row
T-3	payment	→ 1 row
T-4	account	dropped (no event)

outcomes (right table): 5 rows

outcome_id	ticket_id	status	in the result
O-1	T-1	escalated	used
O-2	T-1	resolved	used
O-3	T-2	resolved	used
O-4	T-3	resolved	used
O-5	T-9	resolved	not used (no ticket T-9)

• matched once
• repeated: ticket matched more than once
• no match: kept by LEFT, NULLs added

Result of the INNER JOIN: 4 rows

ticket_id	outcome_id	status	why
T-1	O-1	escalated	repeated
T-1	O-2	resolved	repeated
T-2	O-3	resolved	matched once
T-3	O-4	resolved	matched once

An inner join drops a ticket with no event and repeats a ticket with two events. A left join keeps the ticket with no event. A repeated event repeats its ticket once more in both joins.

Text description of the explorer

Two input tables and one result table. The tickets table has 4 rows: T-1 (delivery), T-2 (returns), T-3 (payment) and T-4 (account). The outcomes table has 5 rows: O-1 and O-2 for T-1, O-3 for T-2, O-4 for T-3, and O-5 for T-9, which is not in tickets. Buttons choose INNER JOIN or LEFT JOIN. A switch adds O-6, a copy of O-3 for T-2. A label on each result row says “matched once”, “repeated” (its ticket matched more than once) or “kept, NULLs” (a left join row with no match). The inner join gives 4 rows for 3 tickets, and the left join 5 rows for 4 tickets. With the repeated event, T-2 appears twice: the inner join gives 5 rows and the left join 6 rows. O-5 is never in the result.

The rule: after a join, each left row gives one result row per match. In a left join, a left row with no match still gives one row. One match keeps the count. Zero matches drop a row (inner) or keep it with NULLs (left). Two or more matches repeat the row.

Worked example: join the real tables

Mei wants each ticket with its customer’s segment and its outcome events. Before she writes the report, she counts the rows of each join. Each query in brackets in this file gives one number, so the result is one row of counts:

queries/24-join-counts.sql

-- Row counts before and after each join. Each query in brackets gives one number.
SELECT
    (SELECT COUNT(*) FROM tickets) AS ticket_rows,
    (SELECT COUNT(*)
       FROM tickets AS t
       INNER JOIN customers AS c ON c.customer_id = t.customer_id) AS inner_customers,
    (SELECT COUNT(*)
       FROM tickets AS t
       LEFT JOIN customers AS c ON c.customer_id = t.customer_id) AS left_customers,
    (SELECT COUNT(*)
       FROM tickets AS t
       INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id) AS inner_outcomes,
    (SELECT COUNT(*)
       FROM tickets AS t
       LEFT JOIN outcomes AS o ON o.ticket_id = t.ticket_id) AS left_outcomes;

Output of python3 q.py queries/24-join-counts.sql

ticket_rows  inner_customers  left_customers  inner_outcomes  left_outcomes
-----------  ---------------  --------------  --------------  -------------
1417         1396             1417            1893            1901

1 row

Mei explains each change before she uses any join.

Tickets and customers: the inner join loses 21 rows

Each ticket has at most one customer, and customer_id is unique in customers (Module 1 checked it). So this join cannot repeat a ticket. The left join returns exactly 1,417 rows, the same as tickets. The inner join returns 1,396, so 21 ticket rows have no customer. Mei finds them with a left join and IS NULL on the right side:

SELECT t.customer_id, COUNT(*) AS ticket_rows
FROM tickets AS t
LEFT JOIN customers AS c ON c.customer_id = t.customer_id
WHERE c.customer_id IS NULL
GROUP BY t.customer_id;

customer_id	ticket_rows	Meaning (from Grace)
NULL	12	guest checkout: no account
C-0241	3	deleted account
C-0242	2	deleted account
C-0243	2	deleted account
C-0244	2	deleted account

12 + 9 = 21. These are real tickets. An inner join drops them without a word, so a report by segment built on it misses 21 tickets. With a left join, they stay, with NULL as the segment, and Mei can name them.

The other direction: 46 customers have no ticket. A join that starts from tickets never shows them, and that is correct for a report about tickets. A report about customers starts from customers instead: customers LEFT JOIN tickets returns 1,442 rows, 1,396 matched pairs plus one row for each of the 46 customers.

Tickets and outcomes: the join adds rows

outcomes has one row per event, and a ticket can have several. So joining it repeats tickets, as T-1 in the tiny example. The inner join returns 1,893 rows from 1,417 ticket rows. That is not an error; it is the grain of the result changing from “one ticket” to “one event of one ticket”. The danger is to count it as tickets.

The left join adds 8 rows: 1,901 − 1,893. These are the 8 tickets with no event yet, such as T-11402, created on the last evening of the export. And in the other direction, 4 events have no ticket: T-9990 to T-9993. They are the orphans of the outcomes table, and no join that starts from tickets shows them.

Mei reconciles 1,901 completely, from the source tables:

Step	Rows
Events in outcomes	1,874
− events whose ticket is not in tickets (orphans)	− 4
= events that match a ticket	1,870
+ tickets with no event (one row each in a left join)	+ 8
= left join for the 1,403 distinct tickets	1,878
+ extra rows from the 14 duplicate ticket rows: each repeats all events of its ticket	+ 23
= left join of tickets and outcomes	1,901

Every row is now explained. The last line needs a closer look.

Run SQL in your browser

Try it: the other direction

This join starts from outcomes, so it keeps every event. WHERE t.ticket_id IS NULL keeps only the events with no ticket. You should see 4. Then change the query: start from tickets, join outcomes, keep the rows where o.outcome_id IS NULL, and count them. You should see 8.

SQL query (you can change it)SELECT COUNT(*) AS event_rows FROM outcomes AS o LEFT JOIN tickets AS t ON t.ticket_id = o.ticket_id WHERE t.ticket_id IS NULL;

Misconception: “a join only adds columns”

Many people think a join adds the columns of the second table and keeps the rows of the first. That is true only when each left row matches at most one right row. If the key is not unique on the right side, rows repeat. If the key is not unique on both sides, the repeats multiply.

Ticket T-11149 shows it. It appears twice in tickets (an export page overlap). It has 5 rows in outcomes, and one of them is a repeated event: a retry logged O-01568 again as O-01569.

Run SQL in your browser

Try it: one ticket, ten rows

Predict the number of rows before you run it: 2 ticket rows, 5 event rows. You should see 10 rows; every outcome is there twice. Then add DISTINCT after SELECT and run it again: 5 rows. Is that the right number of events for T-11149?

SQL query (you can change it)SELECT t.ticket_id, o.outcome_id, o.recorded_at, o.status FROM tickets AS t INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id WHERE t.ticket_id = 'T-11149' ORDER BY o.outcome_id;

What is in the source	Count
Rows of T-11149 in tickets	2 (one is a duplicate)
Rows of T-11149 in outcomes	5 (one is a repeated event)
Rows after the join	2 × 5 = 10
Real events of T-11149	4: escalated, resolved, reopened, resolved

SELECT DISTINCT removes the copy from the duplicate ticket row, but not the repeated event: O-01568 and O-01569 have different IDs. So 5 is still 1 too many. A sum or a count of events after this join is wrong by a factor of 2.5, and SQL gives no sign of it.

The 14 duplicate ticket rows and the 30 repeated events are in the whole table, not only in T-11149. Each duplicate ticket row repeats all events of its ticket: that is the +23 in Mei’s table. Module 4 removes both kinds of duplicate in a cleaning step, with a count. Until then, protect each count yourself: count distinct tickets (COUNT(DISTINCT t.ticket_id)), never rows, after a join with outcomes.

Common mistake

A join with no condition

Symptom: Tomás wants each ticket with its events, and writes SELECT COUNT(*) FROM tickets JOIN outcomes;. He forgets the ON line. The query answers 2,655,458. That looks like “a lot of events”. Later he adds customers the same way, and the browser box stops after 8 seconds: “Stopped after 8 seconds. Does a join match every row with every row?”

Cause: without a condition, a join matches every row of the left table with every row of the right table. This is a cross join: 1,417 × 1,874 = 2,655,458 rows. With a third table, the result has billions of rows, and the query takes too long to finish.

Diagnose: compare the row count after the join with the row counts before it. A result larger than the product of “rows × the most matches for one row” is impossible for a correct join. If the result is close to rows × rows, a condition is missing.

Fix: give every join an ON condition on the key: JOIN outcomes AS o ON o.ticket_id = t.ticket_id. Then count again: 1,893 rows.

Consequence in AI work: the quiet case is worse than the slow one. A wrong condition, such as joining on customer_id instead of ticket_id, still finishes. It gives each ticket the events of all the customer’s tickets. Features or targets built from that result are wrong for every row, and the model learns from them.

Run SQL in your browser

Try it: a query that cannot finish

A comma between tables is another way to write a cross join. This query asks for 1,417 × 1,417 × 1,417 rows, about 2.8 billion. Press Run and wait: the box stops it after 8 seconds. On your computer, python3 q.py (python q.py on Windows) does not stop it; press Ctrl+C to end it there.

SQL query (you can change it)SELECT COUNT(*) AS result_rows FROM tickets AS a, tickets AS b, tickets AS c;

Guided practice: find the unmatched rows

Before you trust a join, list the rows that have no match, in both directions. Save the checks as one file per direction.

1. Open a terminal in ~/projects/ticket-data.

2. Make the file queries/24-join-counts.sql with Mei’s count query from the worked example.

3. Run it:

   macOS

   python3 q.py queries/24-join-counts.sql

   Windows

   python q.py queries/24-join-counts.sql

   Check: the output is 1417 1396 1417 1893 1901.

4. In the box below, write a query that lists the orphan events: the events whose ticket is not in tickets.

5. When the check passes, save the query as queries/25-orphan-events.sql, and run it with python3 q.py queries/25-orphan-events.sql (python q.py … on Windows). Check: 4 rows.

Run SQL in your browser

Your turn: orphan events

The starting query lists all 1,874 events. Add a join to tickets and a WHERE line, so that only the events with no ticket remain. When it is correct, you see 4 rows, for T-9990 to T-9993, and the check passes.

SQL query (you can change it)-- Events whose ticket is not in tickets. -- Show outcome_id, ticket_id and status, sorted by outcome_id. SELECT o.outcome_id, o.ticket_id, o.status FROM outcomes AS o -- add the join and the condition here ORDER BY o.outcome_id;

Show the example answer

queries/25-orphan-events.sql

-- Events whose ticket is not in tickets.
SELECT o.outcome_id, o.ticket_id, o.status
FROM outcomes AS o
LEFT JOIN tickets AS t ON t.ticket_id = o.ticket_id
WHERE t.ticket_id IS NULL
ORDER BY o.outcome_id;

The left join keeps every event. For an event with no ticket, every column of t is NULL, so t.ticket_id IS NULL finds exactly those rows. An inner join cannot find them: it drops them. All four are resolved events. Test t.ticket_id, the key you joined on, and not another column of t: another column can be NULL in a row that did match.

Independent variation: customers with no ticket

Omar asks: “Which segments do the customers with no ticket belong to? Maybe a whole segment never contacts us.”

Write a query that starts from customers, finds the customers with no ticket, and counts them per segment. Save it as queries/26-quiet-customers.sql. Before you run it, predict the total: you know it from the worked example.

Show the example answer

queries/26-quiet-customers.sql

SELECT c.segment, COUNT(*) AS customers_without_tickets
FROM customers AS c
LEFT JOIN tickets AS t ON t.customer_id = c.customer_id
WHERE t.ticket_id IS NULL
GROUP BY c.segment
ORDER BY customers_without_tickets DESC;

segment	customers_without_tickets
home	38
business	4
trade	3
Trade	1

The total is 46. Trade with a capital T is the messy category again: with LOWER(c.segment), trade has 4. No segment is missing completely: most of the quiet customers are home customers, the largest segment (166 of 240). Here COUNT(*) counts customers correctly, because each customer with no ticket has exactly one row after the left join.

Check your understanding

tickets has 1,417 rows. tickets INNER JOIN customers returns 1,396 rows, and tickets LEFT JOIN customers returns 1,417. What does each number tell you?

Show the answer

The left join keeps every ticket row and returns exactly 1,417. So no ticket matched more than one customer: customer_id is unique in customers, and the join adds columns only. The inner join returns 21 rows fewer, so 21 ticket rows have no matching customer. They are 12 guest tickets with NULL customer_id and 9 tickets of 4 deleted accounts (C-0241 to C-0244). A report that uses the inner join loses those 21 rows without an error.

Check your understanding

Predict: ticket A has 2 identical rows in tickets and 3 events in outcomes, with no repeated event. How many rows does tickets INNER JOIN outcomes give for A, and what does COUNT(DISTINCT o.outcome_id) give?

Show the answer

2 × 3 = 6 rows: each ticket row matches all three events. COUNT(DISTINCT o.outcome_id) gives 3, the real number of events, because each event has its own ID. It works here only because there is no repeated event. A repeated event has a new outcome_id, so for a ticket like T-11149 the distinct count of IDs is still too high.

Check your understanding

Explain the difference between an inner join and a left join in one sentence each, and say which one you use to find unmatched rows.

Show the answer

An inner join keeps only the pairs of rows that match the condition, so a row with no match is dropped. A left join keeps every row of the left table, and a left row with no match gets NULL in the right table’s columns. To find unmatched rows, start from the table whose unmatched rows you want. Use a left join, and keep the rows where the right table’s key IS NULL.

Module practice

The support team wants a report: ticket volume and resolution outcomes by customer segment. Priya will also use it to check the escalation rate per group before she trains the model. The report must count tickets, not rows, and it must include the tickets that have no customer.

A worked example on the tiny tables

Mei builds the report on the tiny tables first, where she can see every row. Customer segments: C-1 is home, C-2 is trade, C-3 is business.

Step 1: join, and look at the rows. Two left joins, so that no ticket is lost:

SELECT t.ticket_id, c.segment, o.outcome_id, o.status
FROM tickets AS t
LEFT JOIN customers AS c ON c.customer_id = t.customer_id
LEFT JOIN outcomes AS o ON o.ticket_id = t.ticket_id
ORDER BY t.ticket_id, o.outcome_id;

ticket_id	segment	outcome_id	status	Note
T-1	home	O-1	escalated	T-1 repeated: 2 events
T-1	home	O-2	resolved
T-2	home	O-3	resolved
T-3	trade	O-4	resolved
T-4	NULL	NULL	NULL	guest (no customer), no event

5 rows for 4 tickets. C-3 has no ticket, and O-5 has no ticket: neither can appear in a report that starts from tickets.

Step 2: name the groups. A ticket with no customer_id is a guest. A ticket whose customer_id is not in customers is unknown (a deleted account). Mei writes this with CASE. LOWER(c.segment) puts Trade and trade in one group, for this report only.

Step 3: count distinct tickets per group. COUNT(*) would give home 3; it has 2 tickets.

customer_group	tickets	resolved	escalated	no_event
home	2	2	1	0
trade	1	1	0	0
guest	1	0	0	1
Total	4

The total is 4, the number of distinct tickets in tickets. The report reconciles.

Do not give an alias the name of a column

If you write END AS segment and then GROUP BY segment, SQLite groups by the column c.segment, not by your CASE result. The report then has two trade rows and no guest row, and gives no error. Use a new name, such as customer_group.

Your task

Write the same report on the real tables, in queries/27-segment-report.sql. It has five columns, in this order:

1. the customer group
2. the number of tickets
3. the tickets with at least one resolved event
4. the tickets with at least one escalated event
5. the tickets with no event.

Use the groups home, trade, business, guest and unknown. Then reconcile the total with the source tables, and explain the rows that have no match.

Run SQL in your browser

Module task: the segment report

The starting query runs, and its numbers look plausible: 4 groups, 1,863 in total. Fix it so that it counts distinct tickets, keeps every ticket, names the guest and unknown groups, and adds the three outcome columns. When it is correct, you see 5 rows, the tickets add up to 1,403, and the check passes.

SQL query (you can change it)-- Ticket volume and outcomes by customer group. -- Count distinct tickets. Keep the tickets with no customer. SELECT c.segment AS customer_group, COUNT(*) AS tickets FROM tickets AS t INNER JOIN customers AS c ON c.customer_id = t.customer_id INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id GROUP BY customer_group;

Show the model answer

queries/27-segment-report.sql

-- Ticket volume and outcomes by customer group.
-- Counts distinct tickets, so duplicate rows and repeated events count once.
SELECT
    CASE
        WHEN t.customer_id IS NULL THEN 'guest'
        WHEN c.customer_id IS NULL THEN 'unknown'
        ELSE LOWER(c.segment)
    END AS customer_group,
    COUNT(DISTINCT t.ticket_id) AS tickets,
    COUNT(DISTINCT CASE WHEN o.status = 'resolved' THEN t.ticket_id END) AS resolved,
    COUNT(DISTINCT CASE WHEN o.status = 'escalated' THEN t.ticket_id END) AS escalated,
    COUNT(DISTINCT CASE WHEN o.outcome_id IS NULL THEN t.ticket_id END) AS no_event
FROM tickets AS t
LEFT JOIN customers AS c ON c.customer_id = t.customer_id
LEFT JOIN outcomes AS o ON o.ticket_id = t.ticket_id
GROUP BY customer_group
ORDER BY tickets DESC;

Output of python3 q.py queries/27-segment-report.sql

customer_group  tickets  resolved  escalated  no_event
--------------  -------  --------  ---------  --------
trade           634      632       107        2
home            403      399       44         2
business        345      340       70         4
guest           12       12        1          0
unknown         9        9         3          0

5 rows

Reconciliation with the source tables:

Report column	Report total	Source check	Difference explained
tickets	634 + 403 + 345 + 12 + 9 = 1,403	COUNT(DISTINCT ticket_id) in tickets = 1,403	none: every ticket is in exactly one group
resolved	1,392	distinct tickets with a resolved event in outcomes = 1,396	4: the orphan events of T-9990 to T-9993 are all resolved, and their tickets are not in tickets
escalated	225	distinct tickets with an escalated event = 225; escalated rows = 228	3 repeated escalation events; counted as tickets, they count once
no_event	8	tickets with no row in outcomes = 8	none

Unmatched records:

• Tickets with no customer (21 rows, 21 tickets): 12 guest tickets with NULL customer_id, and 9 tickets of 4 deleted accounts, C-0241 to C-0244. They are in the report as guest and unknown. An inner join would have dropped them.
• Customers with no ticket (46): not in the report, correctly, because it counts tickets.
• Tickets with no event (8): in the report, in no_event. They are recent tickets with no outcome yet.
• Events with no ticket (4): not in the report, because no ticket owns them. Mei lists them for Grace to check in the help-desk system.

The starting query gave 1,863 for three reasons. COUNT(*) counted event rows, not tickets. The inner joins dropped the 21 tickets with no customer and the 8 with no event. And Trade and trade were two groups.

Module check

Check your understanding

What does COUNT(DISTINCT ticket_id) count that COUNT(*) does not, and why must a report after a join with outcomes use it?

Show the answer

COUNT(*) counts rows; COUNT(DISTINCT ticket_id) counts different tickets. After a join with outcomes, a ticket has one row per event, plus extra rows from duplicate ticket rows and repeated events. So COUNT(*) counts events and copies, not tickets. The distinct count gives each ticket once, whatever the join did.

Check your understanding

Predict: Mei changes the first LEFT JOIN of the segment report (to customers) to an INNER JOIN. Which groups change, and what is the new total of the tickets column?

Show the answer

The inner join drops the tickets with no matching customer, so the guest (12) and unknown (9) groups disappear. The other groups do not change. The new total is 1,403 − 21 = 1,382. The report still runs, and nothing says that 21 tickets are missing; only the reconciliation with 1,403 shows it.

Check your understanding

Completion check: run your segment report. Show that its ticket total equals the source, explain why the resolved column is 4 lower than the count of resolved tickets in outcomes, and list every kind of unmatched record with its count.

Show the answer

The tickets column adds up to 634 + 403 + 345 + 12 + 9 = 1,403, which equals COUNT(DISTINCT ticket_id) in tickets. The resolved column is 1,392, while outcomes has 1,396 distinct tickets with a resolved event. The difference is the 4 orphan events (T-9990 to T-9993). They are resolved, but their tickets are not in tickets, so a report that starts from tickets cannot include them. The unmatched records are: 21 ticket rows with no customer (12 guest and 9 from deleted accounts, kept as guest and unknown); 46 customers with no ticket (correctly not in a ticket report); 8 tickets with no event (in no_event); 4 events with no ticket (listed separately for Grace).

See it end to endExplain an online shop's salesCase study

The online-shop case study shows a join that turns £16.2 million of UK sales into £1.018 billion, and how to avoid it.

Recap

In this module you asked the database questions, and checked every answer:

• Select and filter: WHERE keeps only the rows where a test is true, so NULL rows drop out of both = 'email' and <> 'email'. Text comparison is exact, so Email is not email. Name your columns, and sort to see the extremes.
• Aggregate: COUNT(*) counts rows, COUNT(column) non-NULL values, and COUNT(DISTINCT ticket_id) tickets: 1,417 rows, 1,403 tickets. Aggregations leave out NULL and calculate correctly on wrong values, so print MIN and MAX next to every mean.
• Join: an inner join drops rows with no match; a left join keeps every left row. A key that is not unique repeats rows, and duplicates on both sides multiply them. Count the rows and the distinct keys before and after each join, and find the unmatched rows in both directions.
• Reconcile: a report is finished when its totals equal the source and every difference has a reason.

Your segment report is one long query. Next, you split a query like it into named steps that you can run and check one at a time.

Next: Readable queries