CodeCapsule

SQL UNION operator

by

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

✅ What UNION 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

SELECT column1, column2, ...
FROM table1

UNION

SELECT column1, column2, ...
FROM table2;

🔹 Example: Combine data from two tables

SELECT name, email FROM customers
UNION
SELECT name, email FROM prospects;

✔ Removes duplicate rows
✔ Returns one combined result set

🔥 UNION ALL (keeps duplicates)

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

SELECT name FROM employees_2023
UNION ALL
SELECT name FROM employees_2024;

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:

SELECT name FROM table_a
UNION
SELECT name FROM table_b
ORDER BY name;

🔥 Practical Examples

1. Combine active and archived orders

SELECT id, total, created_at FROM orders
UNION ALL
SELECT id, total, created_at FROM archived_orders;

2. Get a list of unique cities from multiple tables

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

3. Find all unique tags across two tag tables

SELECT tag FROM post_tags
UNION
SELECT tag FROM product_tags;

🔧 Using UNION inside subqueries

SELECT *
FROM (
SELECT user_id FROM orders
UNION
SELECT user_id FROM logins
) AS all_users
ORDER BY user_id;

🏎 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

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *