Skip to content
BytePatterns

UNION vs UNION ALL: Duplicates, Speed and Column Rules

6 min readBytePatterns

UNION vs UNION ALL: UNION removes duplicate rows and pays for a sort or hash, UNION ALL keeps every row. Column count, names, ORDER BY, and UNION against JOIN.

UNION vs UNION ALL in one line: both stack the rows of two queries into one result, but UNION then removes duplicate rows, while UNION ALL keeps every row and skips that work. It looks like one keyword but it is a different operation: UNION is a set union that must sort or hash the result to find duplicates; UNION ALL is plain concatenation. Every query below runs on SQLite 3.37 through Python's sqlite3, and the PostgreSQL and MySQL rules quoted come from their manuals, as of October 2026.

UNIONUNION ALL
Duplicate rowsremoved, whole-row comparisonkept, every row
Extra worka sort or hash over the resultnone, rows stream through
Rows returnedat most left + rightexactly left + right

The problem it solves

Sometimes the rows you want live in two places with the same shape: this year's orders and the archive, the morning milking sheet and the evening one. You do not want to pair rows side by side, as a join would. You want one list: the rows of the first query followed by the rows of the second. The only real decision is what happens when the same row turns up twice.

The intuition

Picture two printed lists laid end to end.

  • UNION ALL is the tape: stick the second list under the first. Nothing is compared, so the row count is always left plus right.
  • UNION is the tape followed by a cleanup pass that crosses out every repeated row. To find repeats, the engine must either sort all the rows or keep a hash of every row it has seen.

Four rules apply to both, and interviewers like to test each one:

  • Whole rows are compared. ('A17', 24.5) and ('A17', 22.0) are different rows, so both survive UNION. It behaves like DISTINCT over the combined result, which is why a row repeated inside one side disappears too, not only a row repeated across the two sides.
  • Column count must match, and types must be compatible. PostgreSQL calls this being "union compatible". SQLite is looser about types and PostgreSQL is not, so do not lean on SQLite's leniency.
  • Column names come from the first SELECT. Aliases on the later ones are ignored.
  • ORDER BY and LIMIT go last and apply to the whole result. To sort or limit one side alone, MySQL and PostgreSQL let you wrap that side in parentheses.

Then the comparison people mix up. A join widens rows: it pairs each row with matching rows from another table and adds their columns. A union stacks rows: same columns, more rows. If the report needs morning and evening litres side by side, that is a join; if it needs every milking as its own row, that is UNION ALL. The join side of the story is in SQL joins explained visually.

Watch it run

The animation is the lesson's projection on the cow table. FROM cow names the source, the query names two columns, and each row comes back as a tuple of exactly those two values, while SELECT * would carry every column across the wire. Each side of a union is one of these projections, and the columns you name decide what counts as a duplicate. Project only tag and two milkings of the same cow collapse into one row; project tag, litres and they stay apart.

SELECT Basics

Step 1 of 9

A query describes the shape of the answer, not the steps to go and get it.

The same interactive animation as the lesson — step through it with the controls.

The code

The two shapes in SQL. The first answers "which cows were milked today?", the second "list every milking":

SELECT tag FROM morning
UNION
SELECT tag FROM evening
ORDER BY tag;

SELECT tag, litres FROM morning
UNION ALL
SELECT tag, litres FROM evening
ORDER BY tag;

Now for real, with the lesson's cow table split into a morning sheet and an evening sheet. The evening sheet has D08 typed in twice by mistake:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE morning (tag TEXT, breed TEXT, litres REAL);
  CREATE TABLE evening (tag TEXT, breed TEXT, litres REAL);
  INSERT INTO morning VALUES ('A17', 'Jersey', 24.5),
                             ('B03', 'Holstein', 31.0),
                             ('C22', 'Jersey', 19.5);
  INSERT INTO evening VALUES ('A17', 'Jersey', 22.0),
                             ('B03', 'Holstein', 31.0),
                             ('D08', 'Holstein', 18.0),
                             ('D08', 'Holstein', 18.0);  -- typed in twice
""")

def count(sql):
    return db.execute(f"SELECT COUNT(*) FROM ({sql})").fetchone()[0]

print(count("SELECT tag FROM morning UNION SELECT tag FROM evening"))          # 4
print(count("SELECT tag FROM morning UNION ALL SELECT tag FROM evening"))      # 7
print(db.execute("""SELECT tag, litres FROM morning
                    UNION
                    SELECT tag, litres FROM evening
                    ORDER BY tag, litres""").fetchall())
# [('A17', 22.0), ('A17', 24.5), ('B03', 31.0), ('C22', 19.5), ('D08', 18.0)]
print(count("SELECT tag, litres FROM evening UNION SELECT tag, litres FROM morning WHERE 0"))  # 3

Four tags, seven milkings. With tag, litres projected, A17 appears twice because its litres differ, B03 collapses across the two sheets, and the doubled D08 collapses as well. The last line unions the evening sheet with an empty query: four rows in, three out, so UNION removed a duplicate that existed inside one side only.

Column names, the two classic errors, a join for contrast, and the query plan of each operator:

cur = db.execute("""SELECT tag AS cow, litres FROM morning
                    UNION ALL
                    SELECT tag AS animal, litres AS evening_litres FROM evening""")
print([d[0] for d in cur.description])                       # ['cow', 'litres']

for bad in ["SELECT tag, litres FROM morning UNION SELECT tag FROM evening",
            "SELECT tag FROM morning ORDER BY tag UNION SELECT tag FROM evening"]:
    try:
        db.execute(bad)
    except sqlite3.OperationalError as e:
        print(e)
# SELECTs to the left and right of UNION do not have the same number of result columns
# ORDER BY clause should come after UNION not before

print(db.execute("""SELECT m.tag, m.litres, e.litres FROM morning m
                    JOIN evening e ON e.tag = m.tag ORDER BY m.tag""").fetchall())
# [('A17', 24.5, 22.0), ('B03', 31.0, 31.0)]

for op in ("UNION", "UNION ALL"):
    plan = db.execute(f"EXPLAIN QUERY PLAN SELECT tag FROM morning {op} SELECT tag FROM evening")
    print([row[3] for row in plan])
# ['COMPOUND QUERY', 'LEFT-MOST SUBQUERY', 'SCAN morning', 'UNION USING TEMP B-TREE', 'SCAN evening']
# ['COMPOUND QUERY', 'LEFT-MOST SUBQUERY', 'SCAN morning', 'UNION ALL', 'SCAN evening']

The plans say it directly. UNION pushes the rows of both tables through a temp B-tree, SQLite's sorted structure for spotting duplicates; UNION ALL just scans one table, then the other. How to read these lines is covered in how to read a query plan.

Finally the cost, on a generated pair of tables with 200,000 rows and many repeated tags, plus 300 random table pairs checked against plain Python: UNION ALL must equal the two lists added together, and UNION must equal their set:

import random, time

rng = random.Random(17)
big = sqlite3.connect(":memory:")
big.executescript("CREATE TABLE morning (tag TEXT, litres REAL); CREATE TABLE evening (tag TEXT, litres REAL);")
for t in ("morning", "evening"):                              # 100,000 rows each
    big.executemany(f"INSERT INTO {t} VALUES (?, ?)",
                    [(f"T{rng.randrange(60000)}", rng.randrange(10, 40)) for _ in range(100000)])

for op in ("UNION ALL", "UNION"):
    start = time.perf_counter()
    n = big.execute(f"SELECT COUNT(*) FROM (SELECT tag FROM morning {op} SELECT tag FROM evening)").fetchone()[0]
    print(op, n, f"{time.perf_counter() - start:.3f} s")
# UNION ALL 200000 0.008 s   <- on one machine, roughly
# UNION 57855 0.085 s

ok = True
for _ in range(300):                                          # against plain Python
    a = [(rng.choice("xyz"), rng.randint(0, 2)) for _ in range(rng.randint(0, 8))]
    b = [(rng.choice("xyz"), rng.randint(0, 2)) for _ in range(rng.randint(0, 8))]
    t = sqlite3.connect(":memory:")
    t.executescript("CREATE TABLE a (k, v); CREATE TABLE b (k, v);")
    t.executemany("INSERT INTO a VALUES (?, ?)", a)
    t.executemany("INSERT INTO b VALUES (?, ?)", b)
    ok &= sorted(t.execute("SELECT * FROM a UNION ALL SELECT * FROM b")) == sorted(a + b)
    ok &= sorted(t.execute("SELECT * FROM a UNION SELECT * FROM b")) == sorted(set(a + b))
print(ok)                                                     # True

On one machine, roughly, the deduplicating version took about ten times as long. The figure that will not change on your machine is the row count: 200,000 rows went in and 57,855 came out.

The complexity

  • UNION ALL: O(n + m). Rows stream out as they are read, with no extra memory.
  • UNION: the same reads plus deduplication, about O(k log k) with a sort or O(k) on average with a hash, where k = n + m, and memory or temp space for every row seen.
  • Wide rows cost more to deduplicate, because every column takes part in the comparison.

Where it goes wrong

  • Using UNION by habit. If the two sides cannot overlap, for example rows split by year into two tables, UNION pays for a dedup that finds nothing. Write UNION ALL.
  • Using UNION ALL where duplicates are wrong. A mailing list built from customers and newsletter subscribers needs UNION, or one person gets two emails.
  • Expecting UNION to deduplicate on one column. It compares whole rows. Project only the columns that define "the same row".
  • Putting ORDER BY in the first SELECT. It belongs after the last one, or inside parentheses where the engine allows that.
  • Trusting the column names of the second query. The result is named by the first.

When it shows up in interviews

It is a standard SQL warm-up, "what is the difference, and which is faster?", with follow-ups on the rules above. It also appears inside bigger answers, such as emulating a FULL OUTER JOIN on an engine without one, which is built from UNION ALL; see INNER vs LEFT vs FULL JOIN. The projection behind each side is the subject of the SELECT basics lesson, and DISTINCT, the same dedup inside a single query, is covered in SQL SELECT basics.

How to say it in an interview

"Both stack the rows of two queries that have the same number of compatible columns, and the result takes its column names from the first query. UNION then removes duplicate rows, comparing whole rows, including duplicates that were inside one side, so it needs a sort or a hash over the combined result. UNION ALL keeps everything and is a plain concatenation. I default to UNION ALL whenever the sides cannot overlap or the duplicates are meaningful, and I use UNION only when I need distinct rows. ORDER BY goes at the end and sorts the whole result."

Frequently asked questions

What is the difference between UNION and UNION ALL?

Both combine the rows of two queries into one result. UNION removes duplicate rows, comparing every column, as DISTINCT would. UNION ALL keeps every row, duplicates included. In both, the column count must match and the column names come from the first query.

Is UNION ALL faster than UNION?

Usually, yes. UNION ALL only concatenates, while UNION must also sort or hash the combined rows to find duplicates; SQLite's plan shows it as UNION USING TEMP B-TREE. If the two sides cannot share rows, UNION ALL returns the same result without that extra work.

Does UNION remove duplicates within the same table?

Yes. UNION deduplicates the whole combined result, so a row repeated inside one side collapses to one copy as well, even when the other side is empty. If those rows must be kept, use UNION ALL.

Sources