SQL INNER JOIN: Explain Matching Rows and Repeated Keys

The practice task

Match workshop attendance to a participant directory. A directory entry can have several attendance rows. Show the matched workshops while keeping an attendance entry with an unknown participant out of the matched result.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE people(id INTEGER, name TEXT);
CREATE TABLE attendance(person_id INTEGER, workshop TEXT);
INSERT INTO people VALUES (1,'Ari'),(2,'Bo'),(3,'Cy');
INSERT INTO attendance VALUES (1,'SQL'),(1,'Writing'),(2,'SQL'),(9,'Design');

SELECT p.name, a.workshop
FROM people AS p
INNER JOIN attendance AS a ON a.person_id = p.id
ORDER BY p.name, a.workshop;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Ari appears twice because two attendance records match the same directory ID. Bo appears once. Cy has no attendance match, and the record for person 9 has no directory match. Neither appears in this inner-join result.

The repeated Ari row is not automatically a duplicate to remove. The two workshops represent different facts. Selecting DISTINCT p.name alone discards the workshop detail. DISTINCT applied to both name and workshop retains both Ari rows.

Before joining larger tables, write down the expected relationship: one person to many attendance records here. Then inspect the number of matches per key. Repeated directory IDs would multiply these attendance rows again, which is a different data-quality problem from legitimate repeat attendance.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Join on stable identifiers instead of display names when identifiers exist.
• A missing ON condition can create a Cartesian product.
• DISTINCT can conceal a bad join rather than repair it.