Isn't removing duplicates a good thing?

When you asked for unique emails across two sources, yes — UNION is correct. When you asked to stack two event lists (every trial row and every paid row), a person in both should appear twice. UNION deletes one.

Why would the same person be in both queries?

They trialled and later paid. They appear in last week's signups and this week's conversions. The two SELECTs overlap on purpose. UNION treats that overlap as a duplicate to kill.

Does column order matter?

UNION compares the whole row. If both sides select the same email and date, one row wins. If you add a source column ('trial' vs 'paid'), the rows are no longer equal and UNION keeps both — that's often the clearer query anyway.

Is this the same as COUNT(DISTINCT) dropping NULLs?

Same family (something was collapsed), different operator. DISTINCT/UNION collapse equal values. COUNT(column) skips NULL. Check which collapse you asked for.

Which should the AI default to?

UNION ALL plus an explicit source column, unless you said unique. Dedup is a decision. Stacking is the usual 'combine these two lists' request.

UNION vs UNION ALL Duplicates | Missing Rows

You ask for "everyone who signed up or converted this month — one list." The AI UNIONs two queries. A customer you know did both appears once — or, if the selected columns match exactly, you can't tell which event you kept. Nothing errored. UNION deletes duplicate rows. UNION ALL does not.

UNION is DISTINCT across the stack

SELECT email, created_at FROM trials
UNION
SELECT email, created_at FROM conversions

If Ada is in both tables with the same email and same timestamp (or you only selected email), Ada is one row. You asked for a combined list of events. You got a unique-email list.

SELECT email, created_at, 'trial' AS source FROM trials
UNION ALL
SELECT email, created_at, 'paid' AS source FROM conversions

Ada appears twice, labeled. That's usually what "combine" means. The extra column also stops UNION from collapsing them if you forget ALL.

This is the opposite of JOIN fan-out (too many rows). Here rows disappear because they looked equal.

When UNION is actually right

"Unique emails that appear in either table" — UNION (or UNION ALL + DISTINCT in a wrapper). Say unique if that's what you want. Models reach for UNION because the word sounds tidy, the same way they reach for LIKE '%…%' when you wanted = (wildcards).

How to catch it

Compare counts:

COUNT(*) of query A
COUNT(*) of query B
COUNT(*) of the UNION
If A + B > UNION, rows were collapsed. Show UNION vs UNION ALL.

If you expected A+B and got less, it's this — not a missing OR.

The prompt that heads this off

When stacking two SELECTs, use UNION ALL unless I said unique / distinct.
Add a source column so overlapping people stay visible.
If you use UNION, say that duplicate rows will be dropped.

The short version

UNION means "unique rows from both sides." UNION ALL means "every row from both sides." "Combine these lists" is almost always ALL. If someone vanished from a stacked report, check the word after UNION.

Read-only MCP gateway: mcpserver.design.

Related reading: