Joins

FREE~1.5 h2 labs4 questions1 assignment

Context

A join builds one result from two tables by pairing each row of the first with the rows of the second that share a value in a chosen column, so a listing can carry the facts another table keeps about its neighborhood.

The features a model trains on rarely live in one table. Listings hold prices and areas, a regions table holds each neighborhood's distance to the center, another holds school ratings, and the labels often arrive in a table of their own keyed by listing id, so the training table is a join of all of them, rebuilt every time the data is refreshed.

A join goes wrong by changing the number of rows, and it never raises an error while doing it. An inner join drops every listing whose neighborhood has no row in the other table, and a lookup table that holds one neighborhood twice doubles that neighborhood's listings, so a row count or a sum over the result misses the dropped listings and counts the doubled ones twice. Lesson 2.1.1 filtered and ordered one table, and this lesson puts two together and counts what comes out.

Goal

Declare a primary key on a lookup table, and explain why facts about a neighborhood live in a table of their own.
Join two tables with JOIN ... ON, using table aliases and qualified column names.
Keep every left row with LEFT JOIN, and find the rows that matched nothing with IS NULL on the right table's key.
Place a condition on the right table in ON or in WHERE on purpose, predicting which rows each keeps.
Catch a join that multiplies rows by counting before and after it, find the duplicated key, and fix it on the lookup side.

Prerequisites

Optional, since the lesson builds every table it uses from an empty folder. They are here if you want to watch joins run before you write one.

YouTube - "Intermediate SQL Tutorial | Inner/Outer Joins | Use Cases" by Alex The Analyst. Inner, full outer, left and right outer joins between two tables, then two short case studies that put them to use.
YouTube - "Learn MySQL joins in 5 minutes!" by Bro Code. Inner, left and right joins in five minutes, written for MySQL, whose JOIN ... ON form SQLite shares.

Keys and Relationships
INNER JOIN
LEFT JOIN
When a Join Multiplies Rows

Theory

Nothing here is installed. Save each block as a script in ~/dev/bootcamp and run it with python3, as in Lesson 2.1.1, under the name its first comment gives where it gives one.

Keys and Relationships

What. A primary key, unique and never NULL, like the one Lesson 2.1.1 declared on id, identifies one row. A foreign key is a column in one table holding another table's key, so a row can point at the row that describes it. Why. Facts about a neighborhood belong to the neighborhood, not to each listing: kept once in their own table they are corrected once, while a distance copied into every listing has to be corrected in every copy, and one missed copy contradicts the rest. When. Give every lookup table a primary key on the column other tables match against, before the first row goes in.

build_housing_db.py opens the lesson. It drops and recreates both tables it needs, listings exactly as Lesson 2.1.1 built it and a new regions, so it runs from an empty folder and a second run never doubles a row.

# 'build_housing_db.py', rebuilds both tables on every run
import sqlite3

LISTINGS = [
    (1, "Downtown", 472000, 88.0, 3, "good", "2024-03-02"),
    (2, "Riverside", 318500, 74.5, 2, "fair", "2024-01-15"),
    (3, "Hillcrest", 389000, 120.0, 4, "excellent", "2024-02-20"),
    (4, "Eastgate", 228000, 61.0, 2, "poor", "2024-03-11"),
    (5, "Downtown", None, 95.0, 3, "good", "2024-02-08"),
    (6, "Northend", 274500, 102.5, 4, None, "2024-01-29"),
    (7, "Riverside", 420000, 110.0, 3, "good", "2024-03-18"),
    (8, "Lakeview", 351000, 97.0, 3, "excellent", "2024-02-14"),
    (9, "Southgate", 199000, 58.0, 1, "fair", "2024-01-07"),
    (10, "Hillcrest", 389000, 105.0, 3, "good", "2024-03-25"),
    (11, "Downtown", 512000, 101.0, 4, "excellent", "2024-01-22"),
    (12, "Eastgate", 300000, 83.0, 3, "fair", "2024-02-27"),
]
REGIONS = [
    ("Downtown", "Central", 1.2),
    ("Riverside", "Central", 3.5),
    ("Riverside", "Central", 3.7),
    ("Hillcrest", "North", 7.8),
    ("Eastgate", "East", 5.1),
    ("Northend", "North", 9.4),
    ("Lakeview", "West", None),
]

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS listings")
con.execute("DROP TABLE IF EXISTS regions")
con.execute("""
    CREATE TABLE listings (
        id INTEGER PRIMARY KEY,
        neighborhood TEXT NOT NULL,
        price INTEGER,
        area_sqm REAL NOT NULL,
        rooms INTEGER NOT NULL,
        condition TEXT,
        listed_on TEXT NOT NULL
    ) STRICT
""")
# no key on 'neighborhood', so nothing refused the second Riverside row
con.execute("""
    CREATE TABLE regions (
        neighborhood TEXT,
        region TEXT,
        distance_km REAL
    ) STRICT
""")
con.executemany("INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)", LISTINGS)
con.executemany("INSERT INTO regions VALUES (?, ?, ?)", REGIONS)
con.commit()
listings = con.execute("SELECT * FROM listings").fetchall()
regions = con.execute("SELECT * FROM regions").fetchall()
print(len(listings), len(regions))  # 12 7
con.close()

Each listing names its neighborhood, and regions holds a neighborhood's region and its distance to the center in kilometers, Lakeview's distance unknown. One region row describes many listings, three in Downtown's case, a one-to-many relationship, and listings.neighborhood is a foreign key in all but declaration, since every value in it is meant to match a regions.neighborhood. The table was declared without a key, which is how a second Riverside row, saying 3.7 where the first says 3.5, got in. Declared with one, it refuses the duplicate:

# 'keyed_regions.py'
import sqlite3

mem = sqlite3.connect(":memory:")
mem.execute("""
    CREATE TABLE regions (
        neighborhood TEXT PRIMARY KEY,
        region TEXT,
        distance_km REAL
    ) STRICT
""")
mem.execute("INSERT INTO regions VALUES (?, ?, ?)", ("Riverside", "Central", 3.5))
mem.execute("INSERT INTO regions VALUES (?, ?, ?)", ("Riverside", "Central", 3.7))

Traceback (most recent call last):
  File "/home/goku/dev/bootcamp/keyed_regions.py", line 13, in <module>
    mem.execute("INSERT INTO regions VALUES (?, ?, ?)", ("Riverside", "Central", 3.7))
sqlite3.IntegrityError: UNIQUE constraint failed: regions.neighborhood

REFERENCES declares a foreign key in the schema, and SQLite enforces it only on a connection that ran PRAGMA foreign_keys = ON, the setting command from Lesson 2.1.1, while no transaction was open, which right after connecting is always the case. A connection that has not accepts a row that points at nothing.

import sqlite3

mem = sqlite3.connect(":memory:")
mem.execute("PRAGMA foreign_keys = ON")  # off until a connection asks
mem.execute("CREATE TABLE regions (neighborhood TEXT PRIMARY KEY) STRICT")
mem.execute("""
    CREATE TABLE listings (
        id INTEGER PRIMARY KEY,
        neighborhood TEXT NOT NULL REFERENCES regions (neighborhood)
    ) STRICT
""")
mem.execute("INSERT INTO regions VALUES ('Riverside')")
mem.execute("INSERT INTO listings VALUES (2, 'Riverside')")
try:
    mem.execute("INSERT INTO listings VALUES (9, 'Southgate')")
except sqlite3.IntegrityError as err:
    print(err)                           # FOREIGN KEY constraint failed
mem.close()

DON'T:

DON'T create a lookup table without a primary key. Nothing stopped the second Riverside row, and the last subtitle shows what it costs.
DON'T read REFERENCES as a guarantee. On a connection without PRAGMA foreign_keys = ON, a Southgate listing goes in with no region row and no error.
DON'T switch foreign keys on after an insert on the same connection. Inside the transaction that insert opened, the PRAGMA does nothing, and PRAGMA foreign_keys still reads 0.

INNER JOIN

What. JOIN ... ON pairs every row of one table with every row of the other that makes the ON condition true, and a plain JOIN is an inner join, which returns only rows that found a partner. Why. It puts a listing's region and distance in the same row as its price, the row a model trains on, without copying regions into the listings table. When. Use an inner join when a row without a partner should leave the result, and count what left every time.

listings AS l gives the table an alias, a short name used everywhere else in the query, and a column is qualified with its table's alias, l.price, r.region. A column that both tables have, neighborhood here, must be qualified every time, while a column only one table has may stand alone.

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT l.id, l.neighborhood, r.region, r.distance_km
    FROM listings AS l
    JOIN regions AS r ON l.neighborhood = r.neighborhood
    ORDER BY l.id, r.distance_km
""").fetchall()
for row in rows:
    print(row)
con.close()

(1, 'Downtown', 'Central', 1.2)
(2, 'Riverside', 'Central', 3.5)
(2, 'Riverside', 'Central', 3.7)
(3, 'Hillcrest', 'North', 7.8)
(4, 'Eastgate', 'East', 5.1)
(5, 'Downtown', 'Central', 1.2)
(6, 'Northend', 'North', 9.4)
(7, 'Riverside', 'Central', 3.5)
(7, 'Riverside', 'Central', 3.7)
(8, 'Lakeview', 'West', None)
(10, 'Hillcrest', 'North', 7.8)
(11, 'Downtown', 'Central', 1.2)
(12, 'Eastgate', 'East', 5.1)

Listing 9 is missing: regions has no Southgate row, so listing 9 found no partner, and the inner join dropped it without a word. Listings 2 and 7 appear twice, which the last subtitle takes apart. SELECT COUNT(*), which Lesson 2.1.3 teaches with the other aggregates, returns the number of rows a query produces, so it puts numbers on both effects:

import sqlite3

con = sqlite3.connect("housing.db")
COUNTS = [
    "SELECT COUNT(*) FROM listings",
    "SELECT COUNT(*) FROM listings AS l JOIN regions AS r ON l.neighborhood = r.neighborhood",
    "SELECT COUNT(*) FROM listings WHERE neighborhood = 'Southgate'",
    """SELECT COUNT(*) FROM listings AS l JOIN regions AS r ON l.neighborhood = r.neighborhood
       WHERE l.neighborhood = 'Southgate'""",
]
counts = [con.execute(sql).fetchone()[0] for sql in COUNTS]
print(counts)   # [12, 13, 1, 0]
try:
    con.execute("""
        SELECT id, neighborhood, region
        FROM listings JOIN regions ON listings.neighborhood = regions.neighborhood
    """)
except sqlite3.OperationalError as err:
    print(err)  # ambiguous column name: neighborhood
con.close()

The total rose from 12 to 13 while a listing vanished, so a total that looks close to right proves nothing about which rows are in it.

DON'T:

DON'T run an inner join without counting what went in and what came out. Southgate's listing disappeared, and nothing raised.
DON'T leave a column both tables share unqualified. SELECT id, neighborhood, region raised ambiguous column name: neighborhood.
DON'T use a table's full name after giving it an alias. With listings AS l declared, listings.id raises no such column: listings.id.

LEFT JOIN

What. LEFT JOIN keeps every row of the left table, the one written before the join, and fills the right table's columns with NULL where no right row matched. Why. A listing without a region row is still a listing with a price and an area, and a training set meant to hold every listing cannot lose one to a gap in a lookup table. When. Use a left join whenever every left row must survive, and follow it with an IS NULL test on the right table's key to see which rows found nothing.

Every listing comes back at least once. Southgate's returns with None in each regions column, and Lakeview's with a region but no distance, two NULLs with different causes: one row matched nothing, the other matched a row whose distance is unknown. The right table's key is never NULL in a matched row, so testing it separates the two.

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT l.id, l.neighborhood, r.region, r.distance_km
    FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
    WHERE l.id IN (8, 9)
""").fetchall()
print(rows)         # [(8, 'Lakeview', 'West', None), (9, 'Southgate', None, None)]
unmatched = con.execute("""
    SELECT l.id FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
    WHERE r.neighborhood IS NULL
""").fetchall()
print(unmatched)    # [(9,)]
no_distance = con.execute("""
    SELECT l.id FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
    WHERE r.distance_km IS NULL
""").fetchall()
print(no_distance)  # [(8,), (9,)]
con.close()

A comparison on a right-table column is where a left join turns into an inner one without a word. In WHERE, it runs after the join, every NULL-filled row fails it, and those listings leave the result. In ON, it is part of deciding which right rows match, so a listing with no qualifying partner still comes back once, with NULLs. Both queries below ask for every listing, joined to its region only when that region is Central, and print the ids that come back:

import sqlite3

con = sqlite3.connect("housing.db")
in_where = con.execute("""
    SELECT l.id FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
    WHERE r.region = 'Central'
    ORDER BY l.id
""").fetchall()
in_on = con.execute("""
    SELECT l.id FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood AND r.region = 'Central'
    ORDER BY l.id
""").fetchall()
print([row[0] for row in in_where])  # [1, 2, 2, 5, 7, 7, 11]
print([row[0] for row in in_on])     # [1, 2, 2, 3, 4, 5, 6, 7, 7, 8, 9, 10, 11, 12]
con.close()

RIGHT JOIN keeps every row of the right table instead and FULL OUTER JOIN every row of both, and this SQLite has both, though A RIGHT JOIN B returns the same rows as B LEFT JOIN A, the form most code is written in.

DON'T:

DON'T filter a right-table column in WHERE after a LEFT JOIN when every left row must stay. It kept 5 of the 12 listings, while the same condition in ON kept all 12.
DON'T look for unmatched rows with a right-table column that can be NULL on its own. r.distance_km IS NULL returned Lakeview as well as Southgate, and r.neighborhood IS NULL returned Southgate alone.

When a Join Multiplies Rows

What. When the right table holds a matching key more than once, the join returns each matching left row once per match, so some rows of the result are copies, and a left join returns more rows than its left table. Why. Nothing errors: every row count and every sum that takes in the copied rows comes out too large, and a model trained on the result sees the copied listings twice as often as the rest. When. Check every left join against a lookup table: count the left rows, count the joined rows, and when the second is larger, find the duplicated key before anything reads the result.

A left join against a table with one row per key returns exactly as many rows as its left table, one partner or one NULL row each, so the count before and after the join is the check:

import sqlite3

con = sqlite3.connect("housing.db")
before = con.execute("SELECT COUNT(*) FROM listings").fetchone()[0]
after = con.execute("""
    SELECT COUNT(*) FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
""").fetchone()[0]
print(before, after)
riverside = con.execute("""
    SELECT l.id, l.neighborhood, r.distance_km FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
    WHERE l.neighborhood = 'Riverside'
    ORDER BY l.id, r.distance_km
""").fetchall()
print(riverside)
dupes = con.execute("""
    SELECT neighborhood, COUNT(*) FROM regions
    GROUP BY neighborhood HAVING COUNT(*) > 1
""").fetchall()
print(dupes)
con.close()

12 14
[(2, 'Riverside', 3.5), (2, 'Riverside', 3.7), (7, 'Riverside', 3.5), (7, 'Riverside', 3.7)]
[('Riverside', 2)]

Two extra rows, both Riverside: each Riverside listing paired with both Riverside region rows. The last query finds the duplicated key with GROUP BY, which collapses the rows sharing a neighborhood into one, and HAVING, which keeps the groups holding more than one row, both taught in Lesson 2.1.3.

The fix belongs to the lookup table, and it is a decision before it is code. Which of 3.5 and 3.7 is Riverside's distance is a question about the city, answered by whoever owns the data, and once it is answered the table is rebuilt with a key, so the next duplicate is refused instead of joined. Here 3.5 is kept:

import sqlite3

DECIDED = [
    ("Downtown", "Central", 1.2),
    ("Riverside", "Central", 3.5),
    ("Hillcrest", "North", 7.8),
    ("Eastgate", "East", 5.1),
    ("Northend", "North", 9.4),
    ("Lakeview", "West", None),
]

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS regions")
con.execute("""
    CREATE TABLE regions (
        neighborhood TEXT PRIMARY KEY,
        region TEXT,
        distance_km REAL
    ) STRICT
""")
con.executemany("INSERT INTO regions VALUES (?, ?, ?)", DECIDED)
con.commit()
after = con.execute("""
    SELECT COUNT(*) FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
""").fetchone()[0]
print(after)  # 12
con.close()

Everything after this subtitle starts from the duplicated table, so run build_housing_db.py again before the exercises.

DON'T:

DON'T trust a join's result without comparing its count with the left table's. 12 listings became 14 rows, and nothing raised.
DON'T hide the extra rows with DISTINCT. SELECT DISTINCT l.id, r.region collapsed them to 12 only because both Riverside rows say Central, and with r.distance_km selected all 14 stayed.
DON'T let a join choose between duplicate lookup rows. Decide which row is right, keep one, and make the column a PRIMARY KEY so the table refuses the next duplicate.

Lab Exercises

Lab Exercise 1: Parks and Noise for Every Listing

Run build_housing_db.py first. Build a STRICT table amenities with neighborhood as its primary key and two required columns, parks (a whole number) and noise_db (a decimal), in a script that can run again, and insert these rows:

AMENITIES = [
    ("Downtown", 2, 68.5),
    ("Riverside", 4, 52.0),
    ("Hillcrest", 3, 47.5),
    ("Eastgate", 1, 61.0),
    ("Northend", 5, 44.0),
]

Insert a second Downtown row, ("Downtown", 3, 66.0), catching and printing the error. Inner join listings to amenities and print each listing's id, neighborhood, price, parks and noise level in id order. Then print the number of listings beside the number of joined rows, and the neighborhoods the join dropped, found with a Python set difference from Lesson 1.4.3.

Lab Exercise 2: Schools, Counted Before They Are Trusted

Run build_housing_db.py first. A teammate built schools with no key, from these rows:

SCHOOLS = [
    ("Downtown", "Central High", 8.1),
    ("Downtown", "Harbor Academy", 7.4),
    ("Riverside", "River Prep", 6.9),
    ("Hillcrest", "Hill School", 9.0),
    ("Hillcrest", "Crest Academy", 8.6),
    ("Eastgate", "East Primary", 5.8),
    ("Lakeview", "Lake School", 7.7),
]

Build the table, left join listings to it, and print the row count before and after, the neighborhoods schools holds more than once, and the listings that found no school. Then the team wants every listing, with a school attached only when its rating is at least 8: write it with the rating test in WHERE and again in ON, print both row counts, and print the rows of the version that keeps every listing, ordered by id and school.

Solution 1: Parks and Noise for Every Listing

import sqlite3

AMENITIES = [
    ("Downtown", 2, 68.5),
    ("Riverside", 4, 52.0),
    ("Hillcrest", 3, 47.5),
    ("Eastgate", 1, 61.0),
    ("Northend", 5, 44.0),
]

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS amenities")
con.execute("""
    CREATE TABLE amenities (
        neighborhood TEXT PRIMARY KEY,
        parks INTEGER NOT NULL,
        noise_db REAL NOT NULL
    ) STRICT
""")
con.executemany("INSERT INTO amenities VALUES (?, ?, ?)", AMENITIES)
con.commit()
try:
    con.execute("INSERT INTO amenities VALUES (?, ?, ?)", ("Downtown", 3, 66.0))
except sqlite3.IntegrityError as err:
    print(err)

rows = con.execute("""
    SELECT l.id, l.neighborhood, l.price, a.parks, a.noise_db
    FROM listings AS l
    JOIN amenities AS a ON l.neighborhood = a.neighborhood
    ORDER BY l.id
""").fetchall()
for row in rows:
    print(row)
before = con.execute("SELECT COUNT(*) FROM listings").fetchone()[0]
print(before, len(rows))
every = con.execute("SELECT neighborhood FROM listings").fetchall()
all_hoods = {row[0] for row in every}
joined_hoods = {row[1] for row in rows}
print(sorted(all_hoods - joined_hoods))
con.close()

UNIQUE constraint failed: amenities.neighborhood
(1, 'Downtown', 472000, 2, 68.5)
(2, 'Riverside', 318500, 4, 52.0)
(3, 'Hillcrest', 389000, 3, 47.5)
(4, 'Eastgate', 228000, 1, 61.0)
(5, 'Downtown', None, 2, 68.5)
(6, 'Northend', 274500, 5, 44.0)
(7, 'Riverside', 420000, 4, 52.0)
(10, 'Hillcrest', 389000, 3, 47.5)
(11, 'Downtown', 512000, 2, 68.5)
(12, 'Eastgate', 300000, 1, 61.0)
12 10
['Lakeview', 'Southgate']

The key refused the second Downtown row, so every listing matched at most once and the join could only lose rows, which it did: 12 listings in, 10 rows out, Lakeview and Southgate gone.

DON'T:

DON'T carry the joined rows forward as if they were the listings. Listings 8 and 9 are absent from everything computed after this join.
DON'T read the key on amenities as making the join safe. It stops rows multiplying, and the inner join still dropped two listings.

Solution 2: Schools, Counted Before They Are Trusted

import sqlite3

SCHOOLS = [
    ("Downtown", "Central High", 8.1),
    ("Downtown", "Harbor Academy", 7.4),
    ("Riverside", "River Prep", 6.9),
    ("Hillcrest", "Hill School", 9.0),
    ("Hillcrest", "Crest Academy", 8.6),
    ("Eastgate", "East Primary", 5.8),
    ("Lakeview", "Lake School", 7.7),
]

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS schools")
con.execute("""
    CREATE TABLE schools (
        neighborhood TEXT,
        school TEXT,
        rating REAL
    ) STRICT
""")
con.executemany("INSERT INTO schools VALUES (?, ?, ?)", SCHOOLS)
con.commit()

before = con.execute("SELECT COUNT(*) FROM listings").fetchone()[0]
after = con.execute("""
    SELECT COUNT(*) FROM listings AS l
    LEFT JOIN schools AS s ON l.neighborhood = s.neighborhood
""").fetchone()[0]
print(before, after)
print(con.execute("""
    SELECT neighborhood, COUNT(*) FROM schools
    GROUP BY neighborhood HAVING COUNT(*) > 1
""").fetchall())
print(con.execute("""
    SELECT l.id, l.neighborhood FROM listings AS l
    LEFT JOIN schools AS s ON l.neighborhood = s.neighborhood
    WHERE s.neighborhood IS NULL
""").fetchall())
in_where = con.execute("""
    SELECT l.id, s.school FROM listings AS l
    LEFT JOIN schools AS s ON l.neighborhood = s.neighborhood
    WHERE s.rating >= 8
    ORDER BY l.id, s.school
""").fetchall()
in_on = con.execute("""
    SELECT l.id, s.school FROM listings AS l
    LEFT JOIN schools AS s ON l.neighborhood = s.neighborhood AND s.rating >= 8
    ORDER BY l.id, s.school
""").fetchall()
print(len(in_where), len(in_on))
for row in in_on:
    print(row)
con.close()

12 17
[('Downtown', 2), ('Hillcrest', 2)]
[(6, 'Northend'), (9, 'Southgate')]
7 14
(1, 'Central High')
(2, None)
(3, 'Crest Academy')
(3, 'Hill School')
(4, None)
(5, 'Central High')
(6, None)
(7, None)
(8, None)
(9, None)
(10, 'Crest Academy')
(10, 'Hill School')
(11, 'Central High')
(12, None)

Seventeen rows from twelve listings: Downtown's three listings each met two schools, Hillcrest's two did the same, three and two extra rows. The ON version keeps every listing, but listings 3 and 10 still appear twice, because Hillcrest has two schools rated 8 or more, and only a decision about which school a listing gets, or a single number per neighborhood, ends that.

DON'T:

DON'T assume moving a condition into ON cures duplication. It kept every listing, and Hillcrest's two qualifying schools still doubled listings 3 and 10.
DON'T read the WHERE version's 7 rows as 7 listings. They are 5 listings, 1, 3, 5, 10 and 11, with 3 and 10 counted twice.

Theory Assessment

Q1: Where Riverside's Distance Lives

Explain why Riverside's distance belongs in regions and not in each listing row. Then say what a PRIMARY KEY on regions.neighborhood would have done with the second Riverside row, and why a REFERENCES clause on listings.neighborhood does not stop a Southgate listing on a fresh connection.

Hint: Count how many places a corrected distance would have to reach in each design. For the last part, ask what a new connection has switched on.

Q2: Thirteen From Twelve

The inner join of listings and regions returned 13 rows from 12 listings. Name the listing that is missing and say why no error appeared, explain where the extra rows came from, and say why a total of 13, close to 12, proves nothing about the join being right.

Hint: One effect removes a row and another adds two. Ask what a single total can say about two effects pulling in opposite directions.

Q3: The Same Condition in Two Places

Two left joins of listings to regions differ only in where r.region = 'Central' sits, WHERE or ON. Say which returns every listing, which listings the other loses and why, and why WHERE r.distance_km IS NULL is the wrong test for listings that found no region.

Hint: Ask when each condition runs, before or after the NULL-filled rows exist. For the last part, Lakeview has a region row.

Q4: Every Listing, Near or Not

A teammate wants every listing with its distance when the distance is under 5 km, and NULL otherwise, and writes the query below. On the tables build_housing_db.py builds, say how many rows come back and which listing ids, and give every reason the result is not "every listing". Then fix it so each of the 12 listings appears exactly once.

rows = con.execute("""
    SELECT l.id, r.distance_km
    FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood
    WHERE r.distance_km < 5
    ORDER BY l.id, r.distance_km
""").fetchall()

Hint: Two separate problems are in play, one in the query and one in a table. Fixing only the query leaves a count that is still not 12.

Answer 1:

A distance kept in regions is stored once per neighborhood, so correcting Riverside's distance is one change to one row, and every listing that names Riverside sees it on its next join. Copied into each listing, the same correction must reach every Riverside listing, and one missed copy leaves two listings disagreeing about where the same neighborhood is.

A PRIMARY KEY on regions.neighborhood refuses the second Riverside row with sqlite3.IntegrityError: UNIQUE constraint failed: regions.neighborhood, so the duplicate never reaches a join. REFERENCES is recorded in the schema but enforced only on a connection that has switched PRAGMA foreign_keys on, and a fresh connection has not, so the Southgate listing goes in with nothing to point at.

DON'T treat a declared foreign key as checked. Check the connection's PRAGMA foreign_keys first.

Answer 2:

Listing 9, the Southgate listing, is missing. regions has no Southgate row, so listing 9 found no partner, and an inner join returns only rows that found one. Dropping it is the join working as written, which is why nothing raised.

The two extra rows came from Riverside. regions holds Riverside twice, so listings 2 and 7 each paired with both rows and came back twice. One row lost and two gained left the total at 13, one away from 12, and a total cannot say that it is the sum of a loss and a gain. Only the counts behind it, how many listings matched and how many times, say that.

DON'T read a join's total as proof. Count the unmatched rows and the duplicated keys separately.

Answer 3:

The version with the condition in ON returns every listing. There the condition decides which regions rows count as partners, so a listing whose region is not Central finds no partner and still comes back once, with NULLs. In WHERE the condition runs after the join, every NULL-filled row fails it, and the result keeps listings 1, 2, 5, 7 and 11, losing the Hillcrest, Eastgate, Northend, Lakeview and Southgate listings.

r.distance_km can be NULL in a row that did match: Lakeview's region row has no distance. WHERE r.distance_km IS NULL therefore returns Lakeview along with Southgate, while r.neighborhood is never NULL in a matched row, so WHERE r.neighborhood IS NULL returns only the listings that found nothing.

DON'T find unmatched rows with a right-table column that can hold NULL in a matched row. Test the column the join matched on.

Answer 4:

It returns 7 rows, ids [1, 2, 2, 5, 7, 7, 11].

The first problem is in the query. r.distance_km < 5 sits in WHERE, so it runs after the left join, and every row whose distance is 5 or more, or NULL, is dropped. Hillcrest at 7.8, Eastgate at 5.1 and Northend at 9.4 fail it, and so do Lakeview, whose distance is unknown, and Southgate, which matched no region, so 7 of the 12 listings are gone. The second problem is in the table: regions holds Riverside twice, and both of its distances are under 5, so listings 2 and 7 appear twice.

The condition belongs in ON, where a listing without a qualifying region still comes back with NULL:

rows = con.execute("""
    SELECT l.id, r.distance_km
    FROM listings AS l
    LEFT JOIN regions AS r ON l.neighborhood = r.neighborhood AND r.distance_km < 5
    ORDER BY l.id, r.distance_km
""").fetchall()
print(len(rows))  # 14

That keeps every listing and still returns 14 rows, because the duplicate is untouched. With regions rebuilt on a PRIMARY KEY, as the last subtitle rebuilt it, the same query returns 12 rows, one per listing.

DON'T fix the query and stop. A count above 12 after a left join means the lookup table is the next thing to fix.

Assignment

Assignment: An Audit for Every Lookup Join

Before a lookup table is joined into training data, the team wants four things checked. Write join_audit(con, lookup) that returns a dictionary holding the listings count as rows_in, the row count of listings left-joined to the lookup on neighborhood as rows_out, the neighborhoods listings has that the lookup lacks as unmatched, and the neighborhoods the lookup holds more than once as duplicated, both lists sorted.

For unmatched, use EXCEPT: SELECT neighborhood FROM listings EXCEPT SELECT neighborhood FROM regions returns the rows the first query produces that the second does not, each once. A ? placeholder stands for a value and never for a table name, so the lookup's name has to go into the SQL text itself, and the function accepts only the names in a fixed tuple of tables it was written for, raising ValueError for anything else.

Create a zoning table, with no key, from the rows below. Run the audit on regions as the bootstrap built it and on zoning, then call it with "listings; DROP TABLE regions" to show the refusal. Finally, explain in a comment why rows_out - rows_in is 2 for regions, with one duplicated key, but 3 for zoning, with two.

ZONING = [
    ("Downtown", "mixed"),
    ("Downtown", "commercial"),
    ("Riverside", "residential"),
    ("Hillcrest", "residential"),
    ("Eastgate", "industrial"),
    ("Northend", "residential"),
    ("Lakeview", "residential"),
    ("Southgate", "residential"),
    ("Westfield", "residential"),
    ("Westfield", "mixed"),
]

Hints:

SELECT COUNT(*) FROM ? fails with near "?": syntax error, which is SQLite saying a placeholder cannot stand where a name goes.
For each duplicated key, count how many listings carry it.

Solution:

import sqlite3

ZONING = [
    ("Downtown", "mixed"),
    ("Downtown", "commercial"),
    ("Riverside", "residential"),
    ("Hillcrest", "residential"),
    ("Eastgate", "industrial"),
    ("Northend", "residential"),
    ("Lakeview", "residential"),
    ("Southgate", "residential"),
    ("Westfield", "residential"),
    ("Westfield", "mixed"),
]
LOOKUPS = ("regions", "zoning")  # the only names this code puts into SQL


def join_audit(con, lookup):
    if lookup not in LOOKUPS:
        raise ValueError(f"not an audited lookup table: {lookup}")
    rows_in = con.execute("SELECT COUNT(*) FROM listings").fetchone()[0]
    rows_out = con.execute(f"""
        SELECT COUNT(*) FROM listings AS l
        LEFT JOIN {lookup} AS k ON l.neighborhood = k.neighborhood
    """).fetchone()[0]
    unmatched = con.execute(f"""
        SELECT neighborhood FROM listings
        EXCEPT
        SELECT neighborhood FROM {lookup}
        ORDER BY neighborhood
    """).fetchall()
    duplicated = con.execute(f"""
        SELECT neighborhood FROM {lookup}
        GROUP BY neighborhood HAVING COUNT(*) > 1
        ORDER BY neighborhood
    """).fetchall()
    return {
        "rows_in": rows_in,
        "rows_out": rows_out,
        "unmatched": [row[0] for row in unmatched],
        "duplicated": [row[0] for row in duplicated],
    }


con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS zoning")
con.execute("CREATE TABLE zoning (neighborhood TEXT, zone TEXT) STRICT")
con.executemany("INSERT INTO zoning VALUES (?, ?)", ZONING)
con.commit()
print(join_audit(con, "regions"))
print(join_audit(con, "zoning"))
try:
    join_audit(con, "listings; DROP TABLE regions")
except ValueError as err:
    print(err)
con.close()
# Each duplicated key adds (copies - 1) rows for every listing that carries it.
# regions: Riverside twice, 2 Riverside listings, so 2 extra rows.
# zoning: Downtown twice, 3 Downtown listings, so 3 extra rows, and Westfield
# twice adds nothing, because no listing is in Westfield.

{'rows_in': 12, 'rows_out': 14, 'unmatched': ['Southgate'], 'duplicated': ['Riverside']}
{'rows_in': 12, 'rows_out': 15, 'unmatched': [], 'duplicated': ['Downtown', 'Westfield']}
not an audited lookup table: listings; DROP TABLE regions

The extra rows measure duplicated keys weighted by how many listings use them, which is why two duplicated keys in zoning cost 3 rows and Westfield's cost none, and why a lookup table's own duplicate count says nothing about the damage until it meets the table being joined. unmatched and duplicated are the two lists the join's total can hide from each other, so the audit reports them separately. The allow-list is what makes the f-string safe here: the only text it can write into the SQL is one of two names the code itself chose, and any other name is refused before a query is built.

DON'T put a table name from outside your code into SQL text. Check it against the names the code was written for, because no placeholder can carry it.

Additional Resources

Optional, and nothing later depends on either. They go deeper into two things this lesson used once.

Official docs - SQLite, "SQLite Foreign Key Support", section "Enabling Foreign Key Support". Why enforcement is off by default and exactly when PRAGMA foreign_keys takes effect.
Official docs - SQLite, "SELECT", section "Compound Select Statements". UNION, INTERSECT and EXCEPT, the operators that compare whole query results.

Summary

A join is a promise about row counts that SQL never checks for you. Each kind of join answers a different question, an inner join asks which rows have partners and a left join asks what each left row's partner is, if any, and both answer it silently, by dropping a row or by filling one with NULL. The only evidence of what a join did is what you have to ask for, the counts before and after and the keys that matched nothing or more than once, and the lesson's most useful habit is asking for them every time.

Most join failures are not in the join at all, they are in the lookup table it trusts. A key declared once, PRIMARY KEY on the column everything matches against, does more than any amount of care in the query, because it moves the question of which Riverside row is right to the moment the data is written, when someone can still answer it. A condition placed in WHERE instead of ON is a failure that lives in the query, and it looks so much like the right query that only a check of the rows it returns exposes it. Lesson 2.1.3 aggregates over joined rows, where every one of these failures turns into a wrong number.

Cheat Sheet

Part A - Reference Table

Task	Code	Notes
Key a lookup	`neighborhood TEXT PRIMARY KEY`	refuses a duplicate: `UNIQUE constraint failed`
Declare a foreign key	`REFERENCES regions (neighborhood)`	enforced only after `PRAGMA foreign_keys = ON`
Inner join	`FROM listings AS l JOIN regions AS r ON l.neighborhood = r.neighborhood`	unmatched rows dropped, no error
Qualify a column	`l.neighborhood`, `r.region`	shared names must be qualified
Left join	`LEFT JOIN regions AS r ON ...`	every left row, NULL where unmatched
Find unmatched	`WHERE r.neighborhood IS NULL`	test the key, never a column NULL on its own
Right-side filter	`ON ... AND r.region = 'Central'`	in `WHERE` it drops the NULL rows
Count check	`SELECT COUNT(*)` before and after	more rows after a left join means a duplicated key
Find duplicated keys	`GROUP BY neighborhood HAVING COUNT(*) > 1`	run on the lookup table
Keys one side lacks	`SELECT ... EXCEPT SELECT ...`	rows of the first query the second lacks

Part B - Compact Code Block

import sqlite3

con = sqlite3.connect("housing.db")
before = con.execute("SELECT COUNT(*) FROM listings").fetchone()[0]
rows = con.execute("""
    SELECT l.id, l.price, r.region, r.distance_km
    FROM listings AS l
    LEFT JOIN regions AS r
      ON l.neighborhood = r.neighborhood
     AND r.distance_km < 5
""").fetchall()
print(before, len(rows))  # equal, or a key is duplicated
dupes = con.execute("""
    SELECT neighborhood FROM regions
    GROUP BY neighborhood HAVING COUNT(*) > 1
""").fetchall()
con.close()

Previous: Lesson 1

Mark complete

Next: Lesson 3

Questions about this lesson: #module-2 on Discord

Found an error in this lesson? Report it

Joins

Context

Goal

Prerequisites

Table of Contents

Theory

Keys and Relationships

INNER JOIN

LEFT JOIN

When a Join Multiplies Rows

Lab Exercises

Lab Exercise 1: Parks and Noise for Every Listing

Lab Exercise 2: Schools, Counted Before They Are Trusted

Theory Assessment

Q1: Where Riverside's Distance Lives

Q2: Thirteen From Twelve

Q3: The Same Condition in Two Places

Q4: Every Listing, Near or Not

Assignment

Assignment: An Audit for Every Lookup Join

Additional Resources

Summary

Cheat Sheet