Keep duplicate rows visible with UNION ALL

By Batu · English technical notes

To keep duplicate rows visible in SQLite, use UNION ALL. If two selects each return B, plain UNION collapses them into one result row and loses the evidence that there were two occurrences.

The fixture below creates two one-column source tables and seeds each with one B. It deliberately runs UNION first as the failing query for an occurrence-counting task: its output is ['B']. The correction is UNION ALL, which returns both input rows as ['B', 'B']. Assertions make the intended distinction explicit instead of relying on a visual inspection of the terminal.

This choice is about semantics, not a blanket performance rule. UNION is appropriate when the question is “which distinct values occurred?”; UNION ALL is appropriate when the question is “which rows did both sources produce?” If the source tables can themselves contain duplicates, UNION ALL preserves those too. Conversely, adding DISTINCT later would again discard rows, so apply deduplication only when it reflects the result your caller needs.

The example uses the Python standard-library sqlite3 interface and an in-memory database. It needs no newer Python-only API. SQLite’s compound-select documentation specifies that UNION ALL returns rows from both sides, whereas UNION removes duplicates. 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 first_source (value TEXT);
    CREATE TABLE second_source (value TEXT);
    """
)
connection.execute("INSERT INTO first_source VALUES ('B')")
connection.execute("INSERT INTO second_source VALUES ('B')")

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

assert naive == ["B"]
assert corrected == ["B", "B"]
print("schema:", schema)
print("seed:", first_seed, second_seed)
print("UNION:", naive)
print("UNION ALL:", corrected)

Expected output:

schema: ['CREATE TABLE first_source (value TEXT)', 'CREATE TABLE second_source (value TEXT)']
seed: ['B'] ['B']
UNION: ['B']
UNION ALL: ['B', 'B']