Find values present in either table with UNION

By Batu · English technical notes

To find values present in either SQLite table, use UNION, not UNION ALL. UNION combines the two result sets and removes duplicate result rows, so left-side A, B plus right-side B, C becomes A, B, C.

This in-memory fixture makes the difference inspectable. It prints the table definitions and seeded values first, then runs the tempting UNION ALL query. That query is not wrong when occurrences matter, but its two B rows do not meet the requirement of one value per distinct item. The corrected query changes only the compound operator to UNION and orders the final compound result so the assertion and printed output are deterministic.

Each component of a compound select must return the same number of columns. Here both return one text column, value. The duplicate rule applies to the whole selected row: if a future query selects additional columns, rows that share a value but differ in another selected column are not duplicates for UNION purposes. Use a separate aggregation or select only the identity columns when that is not the intended meaning.

The program uses only the Python standard-library sqlite3 module and a :memory: database; it requires no newer Python-only API. SQLite documents that UNION ALL retains all rows while UNION removes duplicate rows in a compound select. SQLite SELECT documentation

AI assistance disclosure: this synthetic example and explanation were prepared with AI assistance.

Example

import sqlite3


def values(connection, sql):
    return [row[0] for row in connection.execute(sql)]


connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    CREATE TABLE left_values (value TEXT);
    CREATE TABLE right_values (value TEXT);
    """
)
connection.executemany("INSERT INTO left_values VALUES (?)", [("A",), ("B",)])
connection.executemany("INSERT INTO right_values VALUES (?)", [("B",), ("C",)])

schema = [row[0] for row in connection.execute(
    "SELECT sql FROM sqlite_master WHERE type = 'table' ORDER BY name"
)]
left_seed = values(connection, "SELECT value FROM left_values ORDER BY value")
right_seed = values(connection, "SELECT value FROM right_values ORDER BY value")
naive = values(
    connection,
    "SELECT value FROM left_values UNION ALL "
    "SELECT value FROM right_values ORDER BY value",
)
corrected = values(
    connection,
    "SELECT value FROM left_values UNION "
    "SELECT value FROM right_values ORDER BY value",
)

assert naive == ["A", "B", "B", "C"]
assert corrected == ["A", "B", "C"]
print("schema:", schema)
print("seed:", left_seed, right_seed)
print("UNION ALL:", naive)
print("UNION:", corrected)

Expected output:

schema: ['CREATE TABLE left_values (value TEXT)', 'CREATE TABLE right_values (value TEXT)']
seed: ['A', 'B'] ['B', 'C']
UNION ALL: ['A', 'B', 'B', 'C']
UNION: ['A', 'B', 'C']