SQL UNION operator

SQL Tutorial

Here is a clear, practical guide to the SQL UNION operator, with examples, rules, and best-practice notes.

What SQL UNION operator Does

UNION combines the result sets of two or more SELECT statements into a single result set.

  • Duplicates are removed (like SELECT DISTINCT).

  • Columns must be compatible (same number of columns and compatible data types).

Basic Syntax


 

Example: Combine data from two tables

  •  Removes duplicate rows
  •  Returns one combined result set

UNION ALL (keeps duplicates)

UNION ALL is faster because it does NOT check for duplicates.

Use when:

  • You want all rows, including duplicates

  • You want better performance on large datasets

 Important Rules

  1. Each SELECT must have the same number of columns

  2. Columns must be in the same order

  3. Columns must be compatible types (e.g., INT with BIGINT, VARCHAR with TEXT)

  4. ORDER BY applies once at the end:

Practical Examples

1. Combine active and archived orders

2. Get a list of unique cities from multiple tables

3. Find all unique tags across two tag tables

 Using UNION inside subqueries

Performance Tips

  •  Use UNION ALL when duplicates don’t matter—much faster
  •  Apply ORDER BY only at the end
  •  Ensure SELECTs are using indexed columns when possible
  •  Avoid mixing very different data types

You may also like...