Compare two in-memory tables with EXCEPT

By Batu · English technical notes

Also published in our primary archive.

To return IDs in one SQLite table but not another, use SELECT id FROM left_ids EXCEPT SELECT id FROM right_ids. For left IDs A, B, C and right IDs B, C, D, the result is A. SQLite SELECT documents compound operators including EXCEPT; Python sqlite3 covers the in-memory standard-library interface.

The example prints both schemas and seed sets. Its naive left join makes the set difference less direct: it returns every left row, with a NULL right-side value for A. That can be useful diagnostic information, but requires another condition to become a left-only query. The corrected EXCEPT statement directly returns the requested one-column result, and the assertion checks that it is exactly [("A",)].

EXCEPT removes duplicate result rows, so it is inappropriate when duplicate multiplicity is meaningful; SQLite has no EXCEPT ALL. Consider NULL identifiers separately too, since compound queries have their own NULL comparison rules. The ORDER BY makes the displayed result deterministic rather than relying on a default SQL order. This standard-library example uses Python 3.6+ f-strings and no files or network access.

AI assistance disclosure: AI contributed to this article, using only a synthetic in-memory dataset.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE left_ids (id TEXT)")
connection.execute("CREATE TABLE right_ids (id TEXT)")
left_seed = [("A",), ("B",), ("C",)]
right_seed = [("B",), ("C",), ("D",)]
connection.executemany("INSERT INTO left_ids(id) VALUES (?)", left_seed)
connection.executemany("INSERT INTO right_ids(id) VALUES (?)", right_seed)

naive = connection.execute(
    """
    SELECT left_ids.id, right_ids.id
    FROM left_ids LEFT JOIN right_ids ON right_ids.id = left_ids.id
    ORDER BY left_ids.id
    """
).fetchall()
left_only = connection.execute(
    "SELECT id FROM left_ids EXCEPT SELECT id FROM right_ids ORDER BY id"
).fetchall()

assert naive == [("A", None), ("B", "B"), ("C", "C")]
assert left_only == [("A",)]
print("schemas: left_ids(id TEXT); right_ids(id TEXT)")
print(f"seed rows: left={left_seed}; right={right_seed}")
print(f"naive left-join result: {naive}")
print(f"corrected EXCEPT result: {[identifier for (identifier,) in left_only]}")

schemas: left_ids(id TEXT); right_ids(id TEXT)
seed rows: left=[('A',), ('B',), ('C',)]; right=[('B',), ('C',), ('D',)]
naive left-join result: [('A', None), ('B', 'B'), ('C', 'C')]
corrected EXCEPT result: ['A']