INNER and OUTER JOINs

SQL: lesson 5 of 10

Match rows across tables — and decide who survives a miss.

Lesson 5 of 10 · 6 min

Step 1 of 11

Two registers: dogs in one, adoptions in the other. A join lines them up on a condition.

The Idea

A join lines up rows from two tables on a matching condition. INNER JOIN keeps only the pairs that matched. LEFT JOIN keeps every left-hand row regardless, padding the right side with NULL. Choosing between them is really choosing what a missing match should mean.

Real-World Example

A rescue shelter keeps dogs in one register and adoptions in another. The inner join is the happy-ending list for the newsletter. The left join is the one the manager actually needs, because the dogs with an empty adoption column are the ones still in the kennels.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE dog (id INTEGER, name TEXT);
  CREATE TABLE adoption (dog_id INTEGER, adopter TEXT);
  INSERT INTO dog VALUES (1, 'Pepper'), (2, 'Rusty'), (3, 'Nell');
  INSERT INTO adoption VALUES (1, 'Yusuf'), (3, 'Marta');
""")
rows = db.execute("""
  SELECT dog.name, adoption.adopter
  FROM dog LEFT JOIN adoption ON adoption.dog_id = dog.id
""").fetchall()
print(rows)
# [('Pepper', 'Yusuf'), ('Rusty', None), ('Nell', 'Marta')]

Your turn

Fill in the blank.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE dog (id INTEGER, name TEXT);
CREATE TABLE adoption (dog_id INTEGER, adopter TEXT);
INSERT INTO dog VALUES (1, 'Pepper'), (2, 'Rusty');
INSERT INTO adoption VALUES (1, 'Yusuf');
""")
# Which dogs are still waiting? Expected: [('Rusty',)]
print(db.execute("""
SELECT dog.name FROM dog ___ JOIN adoption ON adoption.dog_id = dog.id
WHERE adoption.dog_id IS NULL
""").fetchall())

Mini quiz

1 / 3

INNER and OUTER JOINs