Find unmatched rows with a LEFT JOIN

By Batu · English technical notes

Also published in our primary archive.

To find parent rows that have no child rows, keep every parent with a LEFT JOIN and test a non-nullable child-side key for NULL. The join produces normal matches where they exist and supplies NULL values for child columns when no matching child row exists. In this example, customers Ava and Cy have purchases; Ben has none, so the query returns only Ben.

The ON p.customer_id = c.id condition belongs to the join because it defines the relationship. The WHERE p.customer_id IS NULL condition then filters to the unmatched left-side rows. Use a child column that is guaranteed non-NULL for this test. Here purchase.customer_id is the join key and every inserted purchase has a value. Testing some optional child attribute instead could incorrectly label a customer as unmatched when a purchase exists but that attribute is NULL.

The assertions verify the result for this small fixture, not every possible schema or data-integrity condition. A customer with several purchases still appears only as a matched row before filtering, and is excluded by the IS NULL predicate. For other tasks, such as checking a condition on child rows, NOT EXISTS may express the intent more directly. SQLite’s SELECT documentation covers join processing, while Python’s sqlite3 documentation covers executemany() and query cursors. This uses no newer API; Python 3.6+ is required for the f-string. AI assistance disclosure: this article was drafted with AI assistance.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript(
    "CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT);"
    "CREATE TABLE purchase (customer_id INTEGER);"
)
con.executemany(
    "INSERT INTO customer VALUES (?, ?)",
    [(1, "Ava"), (2, "Ben"), (3, "Cy")],
)
con.executemany("INSERT INTO purchase VALUES (?)", [(1,), (1,), (3,)])

unmatched = [
    row[0]
    for row in con.execute(
        "SELECT c.name "
        "FROM customer AS c "
        "LEFT JOIN purchase AS p ON p.customer_id = c.id "
        "WHERE p.customer_id IS NULL "
        "ORDER BY c.id"
    )
]

assert unmatched == ["Ben"]
print(f"without_purchase={unmatched}")
con.close()

without_purchase=['Ben']