Build a Mailing List With UNION

What each one returns

Both operators put the rows of one query under the rows of another. The difference is what happens to repeats. Here's the plain stack of all three email lists:

SELECT COUNT(*) FROM (
  SELECT email FROM customers
  UNION ALL
  SELECT email FROM active_users
  UNION ALL
  SELECT email FROM inactive_users
);

That returns 17. Swap every UNION ALL for UNION and the count drops to 14. Three rows were copies of rows already in the list.

Where the duplicates came from

You can see the repeats by grouping the UNION ALL result:

SELECT email, COUNT(*) AS times
FROM (
  SELECT email FROM customers
  UNION ALL
  SELECT email FROM active_users
  UNION ALL
  SELECT email FROM inactive_users
)
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Three addresses show up twice. Two of them, [email protected] and [email protected], repeat inside the customers table itself (two different customers share each address). The third, [email protected], is a customer and also an inactive user.

That first point surprises people. UNION doesn't only remove rows that appear in both queries. It removes every duplicate in the combined result, including ones that came from the same table. If you want to find those repeats on their own, the Find Duplicate Emails question covers it.

UNION compares whole rows

Duplicates are judged on every selected column together. Add the name and the result changes:

SELECT COUNT(*) FROM (
  SELECT name, email FROM customers
  UNION
  SELECT name, email FROM active_users
  UNION
  SELECT name, email FROM inactive_users
);

This returns 17, the same as UNION ALL. The two customers at [email protected] have different names, and Dave Wilson in customers isn't the same row as Dave in inactive_users. A label column has the same effect:

SELECT email, 'customer' AS source FROM customers
UNION
SELECT email, 'user' FROM active_users
UNION
SELECT email, 'user' FROM inactive_users
ORDER BY email;

That gives 15 rows. The repeats inside customers collapse, but [email protected] stays twice, once per source. So if the task is "each email once", select only the email.

Which one is faster

UNION ALL is usually cheaper. To remove duplicates, the database has to sort or hash the whole combined result, which UNION ALL never does. On a few rows you won't notice. On millions of rows it can be the slowest step in the query.

The usual advice is to default to UNION ALL and reach for UNION only when duplicates are possible and unwanted. When the two sides can't overlap (for example, rows labelled with different literal values, or two tables split by date), UNION ALL gives the same answer for less work. The Combine Two Tables With UNION question is a case like that: the two user tables share no rows, and the status label keeps the sides apart anyway, so UNION ALL is the right choice.

Rules that apply to both

• Every SELECT needs the same number of columns. SELECT email FROM customers UNION SELECT name, email FROM active_users fails with an error about the number of result columns.
• Columns are matched by position, not by name. SELECT email FROM customers UNION SELECT name FROM active_users runs, and puts names under the email heading.
• The output column names come from the first SELECT.
• One ORDER BY goes at the end and sorts the whole result.

What to say in the interview

Lead with the one-line difference: UNION removes duplicates, UNION ALL keeps them. Then add the cost, since UNION does extra work to find the duplicates. Close with when you'd pick each, and mention that duplicates are compared across all selected columns. If you're asked to combine lists and the question says "unique" or "each once", that's a UNION. If it says "all" or you're adding up totals afterward, it's UNION ALL, because removing duplicates there would drop real rows.