MySQL UNION Operator

MySQL UNION Operator: Merging Distinct Result Sets

The MySQL UNION operator combines the rows from two or more SELECT queries into one unified result set and automatically removes duplicate rows. Every SELECT inside a UNION must return the exact same number of columns with compatible data types.

Basic Syntax and Rules

1. Each SELECT statement must list the exact same number of columns.
2. Columns at the same position should have compatible data types (string with string, number with number).
3. Column names in the final output come from the first SELECT statement.

SELECT col1, col2 FROM table_a
UNION
SELECT col1, col2 FROM table_b
ORDER BY col1 ASC;

Practical SQL Example

This query combines active rows from demo_customers and past contacts from demo_archived_customers into one deduplicated mailing list:

SELECT email, city, 'active' AS account_state
FROM demo_customers
WHERE is_active = 1
UNION
SELECT email, city, 'archived' AS account_state
FROM demo_archived_customers
ORDER BY email ASC;

Common Mistakes and Best Practices

• Use UNION ALL When Duplicates Are Impossible: UNION forces MySQL to sort or hash every combined row to eliminate duplicates. When merging disjoint sets (like 'active' vs. 'archived' if emails cannot overlap, or when duplicates are desired), switch to UNION ALL to skip that extra work.
• Wrap Individual ORDER BY / LIMIT Queries in Parentheses: A trailing ORDER BY sorts the entire UNION. If you need to take the top 3 from each branch before merging, wrap each branch: (SELECT ... LIMIT 3) UNION (SELECT ... LIMIT 3).

Frequently Asked Questions

What happens if the two SELECT statements have different column counts?

MySQL rejects the query immediately with error 1222: The used SELECT statements have a different number of columns.

Does UNION sort results automatically?

No, although older MySQL versions sometimes produced sorted output as a side effect of duplicate removal, always add an explicit ORDER BY at the end.

How does UNION treat NULL values during deduplication?

UNION treats identical rows containing NULL in the same positions as duplicates and keeps only one copy.