03.1 INNER JOIN and LEFT JOIN

01 | WHY A SINGLE TABLE IS NOT ENOUGH

Memberships stores IDs, not repeated student and club names. To produce a readable membership list, we need facts from several tables at once.

A join combines rows according to a condition. The stored tables remain separate. Think of building a report from connected facts rather than permanently pasting tables together.

Use this section’s three-table practice database. It is a fresh dataset, separate from Sections 01 and 02. All examples use SQLite. A membership score is an optional fictional activity result from 0 to 100; NULL means not yet recorded, not zero.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
PRAGMA foreign_keys = ON;

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    year_level INTEGER NOT NULL
);
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    fee_cents INTEGER NOT NULL
);
CREATE TABLE Memberships (
    student_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    score INTEGER,
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id)
);

INSERT INTO Students VALUES
    (101, 'Maya', 10), (102, 'Leo', 11), (103, 'Amina', 10),
    (104, 'Noah', 12), (105, 'Maya', 11);
INSERT INTO Clubs VALUES
    (1, 'Robotics', 'Technology', 12, 1500),
    (2, 'Game Design', 'Technology', 16, 0),
    (3, 'Art', 'Creative', 12, 800),
    (4, 'Film', 'Creative', 20, 1000),
    (5, 'Chess', 'Strategy', 16, 0);
INSERT INTO Memberships VALUES
    (101, 1, 'member', 95), (101, 2, 'leader', 85),
    (102, 1, 'member', NULL), (103, 3, 'member', 70),
    (103, 4, 'leader', 90), (104, 2, 'member', 80);
"""
query = """
SELECT club_id, club_name FROM Clubs ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | TRACE THE REFERENCE DATA

Student 105 has no membership. Chess has no members. Student 101 has two memberships. These deliberate cases help us see exactly which rows each join retains.

03 | MATCH TWO TABLES WITH INNER JOIN

SELECT Students.student_id, Students.first_name, Memberships.club_id
FROM Students
INNER JOIN Memberships
    ON Students.student_id = Memberships.student_id
ORDER BY Students.student_id, Memberships.club_id;

For each student, the ON condition matches membership rows with the same student_id. INNER JOIN keeps matching pairs only. Student 105 is absent because no matching membership exists.

Student 101 appears twice because two membership rows match. This is the expected one-to-many result, not an accidental duplicate to delete blindly.

04 | WATCH ONE MATCH AT A TIME

The name Maya is not the join key. Matching by name could connect unrelated students or multiply results incorrectly. Our declared relationship uses the stable student ID.

Both joined tables contain student_id, so qualify it with the table name to avoid an ambiguous reference.

05 | ALIASES MAKE THE QUERY EASIER TO READ

SELECT s.student_id, s.first_name, m.club_id, m.role
FROM Students AS s
INNER JOIN Memberships AS m
    ON s.student_id = m.student_id
ORDER BY s.student_id, m.club_id;

AS s and AS m introduce short table aliases. The dot means “this column from this source”. These aliases last for the query and do not rename stored tables.

Use meaningful short aliases consistently. Choosing explicit output columns also avoids a confusing SELECT * result with repeated key headings.

06 | CONNECT THE THIRD TABLE

SELECT s.student_id, s.first_name, c.club_name, m.role
FROM Students AS s
INNER JOIN Memberships AS m ON s.student_id = m.student_id
INNER JOIN Clubs AS c ON m.club_id = c.club_id
ORDER BY s.student_id, c.club_id;

The first join finds each student’s memberships. The second looks up the club associated with each membership. Now the report can display names while keeping the underlying data separate.

Each join needs the correct relationship. Joining student_id to club_id just because both contain integers would not express the model.

07 | LEFT JOIN PRESERVES THE LEFT SIDE

SELECT s.student_id, s.first_name, m.club_id
FROM Students AS s
LEFT JOIN Memberships AS m ON s.student_id = m.student_id
ORDER BY s.student_id, m.club_id;

LEFT JOIN keeps matching pairs and also represents each unmatched left-side row with NULL values for right-side columns. Student 105 now appears with a NULL club_id.

Left means the source written before LEFT JOIN, not the visually leftmost column in SELECT. Students with several matches still produce several result rows.

08 | FIND THE MISSING MATCHES

SELECT s.student_id, s.first_name
FROM Students AS s
LEFT JOIN Memberships AS m ON s.student_id = m.student_id
WHERE m.student_id IS NULL
ORDER BY s.student_id;

The tested right-side key is NOT NULL in genuine membership rows. Therefore its NULL value identifies the unmatched placeholder.

Do not test m.score IS NULL to find non-members: Leo has a real membership with an unrecorded score. A missing attribute and a missing related row are different.

09 | ON AND WHERE ARE NOT INTERCHANGEABLE

SELECT s.student_id, s.first_name, m.club_id
FROM Students AS s
LEFT JOIN Memberships AS m
    ON s.student_id = m.student_id AND m.role = 'leader'
ORDER BY s.student_id, m.club_id;

Putting the role condition in ON keeps every student while matching leader memberships only. Non-leaders remain with NULL right-side values.

Moving m.role = 'leader' into WHERE instead removes those NULL placeholders, retaining only actual leaders. Decide which question you mean before placing the condition.

10 | AVOID ACCIDENTAL MULTIPLICATION

Without a suitable join condition, combinations can multiply. Five students paired with five clubs would give 25 pairs, whether or not anyone joined anything.

Before running a join, predict what one result row represents. After running it, check missing matches and repeated parent IDs against that meaning.

SELECT c.club_id, c.club_name, m.student_id
FROM Clubs AS c
LEFT JOIN Memberships AS m ON c.club_id = m.club_id
ORDER BY c.club_id, m.student_id;

11 | INDEPENDENT CHALLENGE

SELECT s.student_id, s.first_name, c.club_name
FROM Students AS s
INNER JOIN Memberships AS m ON s.student_id = m.student_id
INNER JOIN Clubs AS c ON m.club_id = c.club_id
WHERE m.role = 'leader'
ORDER BY s.student_id, c.club_id;

Next, we will count and summarise rows. Understanding what a joined row represents will be essential to counting correctly.

KEEP LEARNING

Previous page 03.2 COUNT, SUM, AVG, MIN and MAX

References: SQLite joins and grouping · Aggregate functions · Subquery expressions