SQL Notes
Learn how to use the UNION operator in SQL to combine result sets from multiple SELECT queries while removing duplicates, with syntax, rules, and practical examples.
Imagine you have a list of customers from your Mumbai office and another list from your Delhi office. You want to combine them into a single list without duplicates. The UNION operator does exactly this — it merges the results of two or more SELECT statements into one result set, automatically removing duplicate rows.
UNION is a set operator that treats query results like mathematical sets. Just as combining two sets eliminates duplicates, UNION combines rows from multiple queries and keeps only unique rows in the final output.
In database applications, UNION is essential whenever you have data spread across multiple tables that share the same structure but represent different categories, time periods, or sources. For example, archived orders in one table and current orders in another, or customer contact info split across regional databases. UNION brings them together into a single, clean result set that your application can process uniformly.
Simple Example
Suppose we have two tables tracking customers from different cities:
MumbaiCustomers:
| CustomerID | Name | |
|---|---|---|
| 1 | Rahul Sharma | rahul@email.com |
| 2 | Priya Patel | priya@email.com |
| 3 | Amit Kumar | amit@email.com |
DelhiCustomers:
| CustomerID | Name | |
|---|---|---|
| 4 | Sneha Verma | sneha@email.com |
| 5 | Vikas Gupta | vikas@email.com |
| 2 | Priya Patel | priya@email.com |
Notice that Priya Patel appears in both tables. UNION removes the duplicate:
SELECT Name, Email FROM MumbaiCustomers
UNION
SELECT Name, Email FROM DelhiCustomers;Result (5 rows, not 6):
| Name | |
|---|---|
| Rahul Sharma | rahul@email.com |
| Priya Patel | priya@email.com |
| Amit Kumar | amit@email.com |
| Sneha Verma | sneha@email.com |
| Vikas Gupta | vikas@email.com |
Priya appears only once because UNION eliminates duplicates.
UNION Rules
Rule 1: Same Number of Columns
-- FAILS: Different column counts
SELECT Name, Email FROM Customers
UNION
SELECT ProductName FROM Products; -- Only 1 column!Rule 2: Compatible Data Types
Corresponding columns must have compatible types. You cannot UNION a VARCHAR column with a DATE column:
-- Works: Both columns are VARCHAR
SELECT Name FROM Employees
UNION
SELECT DeptName FROM Departments;
-- FAILS: INT and VARCHAR are incompatible
SELECT Salary FROM Employees
UNION
SELECT Name FROM Employees;Rule 3: Column Names Come from First Query
SELECT Name AS PersonName, Email AS ContactEmail FROM Customers
UNION
SELECT SupplierName, SupplierEmail FROM Suppliers;
-- Result columns are named "PersonName" and "ContactEmail"UNION with Different Tables
UNION is most powerful when combining structurally similar data from different sources:
-- All contacts from customers and suppliers
SELECT Name, Phone, 'Customer' AS ContactType
FROM Customers
UNION
SELECT ContactName, Phone, 'Supplier' AS ContactType
FROM Suppliers;The added literal column 'Customer' or 'Supplier' helps identify where each row came from.
UNION with the Same Table
You can UNION a table with itself using different WHERE conditions:
This pattern might seem redundant at first — why not just use OR in the WHERE clause? The answer is that UNION becomes valuable when the two conditions require fundamentally different query structures, perhaps involving different JOINs or subqueries. It also helps when you want to clearly separate two different logical criteria for readability and maintainability, especially in generated queries from application code.
-- Employees earning more than 100000 OR in Engineering
SELECT EmpID, Name, Department, Salary FROM Employees WHERE Salary > 100000
UNION
SELECT EmpID, Name, Department, Salary FROM Employees WHERE Department = 'Engineering';This is equivalent to:
SELECT EmpID, Name, Department, Salary FROM Employees
WHERE Salary > 100000 OR Department = 'Engineering';But UNION can be clearer when the conditions are complex or come from different logic paths.
UNION with ORDER BY
You can only use ORDER BY once, at the end of the entire UNION:
SELECT Name, City FROM MumbaiCustomers
UNION
SELECT Name, City FROM DelhiCustomers
ORDER BY Name; -- Applies to the combined resultYou cannot ORDER BY individual queries within a UNION:
-- WRONG: Cannot ORDER BY in the middle
SELECT Name FROM MumbaiCustomers ORDER BY Name -- ERROR
UNION
SELECT Name FROM DelhiCustomers;Real-World Example: Combined Event Log
An application stores different types of events in separate tables:
-- Combine login events, purchase events, and support tickets
SELECT
UserID,
EventDate,
'Login' AS EventType,
NULL AS Amount
FROM LoginEvents
WHERE EventDate >= '2026-01-01'
UNION
SELECT
UserID,
PurchaseDate,
'Purchase',
Amount
FROM Purchases
WHERE PurchaseDate >= '2026-01-01'
UNION
SELECT
UserID,
TicketDate,
'Support',
NULL
FROM SupportTickets
WHERE TicketDate >= '2026-01-01'
ORDER BY EventDate DESC;This creates a unified activity timeline for all users.
Real-World Example: Search Across Multiple Tables
-- Search for "Sharma" across customers, employees, and suppliers
SELECT Name, Email, 'Customer' AS Source FROM Customers WHERE Name LIKE '%Sharma%'
UNION
SELECT Name, Email, 'Employee' FROM Employees WHERE Name LIKE '%Sharma%'
UNION
SELECT ContactName, Email, 'Supplier' FROM Suppliers WHERE ContactName LIKE '%Sharma%';How UNION Removes Duplicates
UNION compares entire rows to determine duplicates. Two rows are duplicates only if ALL columns match:
SELECT 'Rahul', 'Engineering'
UNION
SELECT 'Rahul', 'Marketing';
-- Returns 2 rows (different department)
SELECT 'Rahul', 'Engineering'
UNION
SELECT 'Rahul', 'Engineering';
-- Returns 1 row (complete duplicate removed)Performance Considerations
UNION must sort and compare all rows to remove duplicates. This has a performance cost:
- The database performs a sort or hash operation on the combined result
- For large result sets, this can be slow
- If you know there are no duplicates (or duplicates are acceptable), use UNION ALL instead — it skips the deduplication step and runs significantly faster
-- UNION: Removes duplicates (slower)
SELECT Name FROM Table1 UNION SELECT Name FROM Table2;
-- UNION ALL: Keeps all rows including duplicates (faster)
SELECT Name FROM Table1 UNION ALL SELECT Name FROM Table2;Common Mistakes to Avoid
Mistake 1: Mismatched column counts. Always ensure both SELECT statements return the same number of columns. Use NULL as a placeholder for missing columns.
Mistake 2: Forgetting that UNION removes duplicates. If you need all rows including duplicates, use UNION ALL.
Mistake 3: Putting ORDER BY in the wrong position. ORDER BY goes only at the very end, after all UNION operations.
Mistake 4: Expecting column names from the second query. Result column names always come from the first SELECT. Name your columns there.
Best Practices
- Use UNION ALL when duplicates are impossible or acceptable — it is much faster
- Add a source identifier column to track which query contributed each row
- Match column types carefully to avoid implicit conversion issues
- Place ORDER BY at the end to sort the final combined result
- Use NULL as placeholder when one query has fewer meaningful columns
- Consider performance — UNION on millions of rows can be expensive
UNION is your tool for combining related data from multiple sources into a single, clean result set. Whether you are merging regional data, creating unified reports, or searching across tables, UNION makes it possible with elegant simplicity.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for UNION Operator in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, set, operators, union
Related SQL Complete Guide Topics