? MySQL UNION

? Quick Overview

The UNION operator combines the result sets of two or more SELECT statements and returns distinct rows (duplicates removed). Use UNION ALL when you want to keep duplicates.

? Key Concepts

Distinct results — UNION removes duplicate rows by default.
Column compatibility — Each SELECT must return the same number of columns with compatible types.
ORDER BY — Apply ORDER BY once at the end to sort the combined results.
UNION vs UNION ALL — UNION removes duplicates; UNION ALL retains them.

⚙️ Syntax / Theory

? View Code Example

-- Basic UNION syntax: 
combine two SELECT results into one distinct result set
SELECT columns
FROM table1
UNION
SELECT columns
FROM table2;

? Code Examples

? View Code Example

-- Example: get all unique product names from two product tables
SELECT product_name FROM products_electronics
UNION
SELECT product_name FROM products_furniture;

? View Code Example

-- Example: unique customer names from north and south regions
SELECT customer_name FROM customers_north
UNION
SELECT customer_name FROM customers_south;

? View Code Example

-- Example: retain duplicates using UNION ALL
SELECT city FROM region_a
UNION ALL
SELECT city FROM region_b;

? Live Output / Explanation

What happens behind the scenes

When the server executes a UNION, it runs each SELECT, concatenates the results, and then performs a distinct operation (de-duplication). This can have performance implications for large result sets; UNION ALL is faster when de-duplication is not needed.

Important: column order and compatible data types matter — mismatched numbers or incompatible types will cause errors.

✅ Tips & Best Practices

Prefer UNION ALL when you don't need to remove duplicates — it's more efficient.
Ensure SELECT statements return the same number of columns and compatible types (or use explicit casts).
Use ORDER BY once at the end to sort the final combined result.
To control columns, use aliases in the first SELECT — they become the column names for the combined set.

? Try It Yourself / Practice Tasks

Combine employee names from two department tables and return only unique names.
Fetch all cities from two region tables using UNION, then change to UNION ALL and observe differences.
Write a UNION query that also sorts results by name using ORDER BY at the end.