SQL

UNION in SQL

🔗 The UNION operator in SQL is used to combine the results of two or more SELECT statementsinto a single result set. By default, UNION removes duplicate rows, returning only distinct records.

📖 What is UNION?

The UNION operator merges the output of multiple queries that have the same number of columns and compatible data types. It treats the combined result as a single table.

Information

By default, UNION removes duplicate rows. If you want to keep duplicates, use UNION ALL instead.

🎯 Why Use UNION?

UNION is useful when data is stored in multiple tables or when multiple queries return similar information that should be displayed together.

📌 Combine results from multiple tables.
📌 Merge data from multiple queries.
📌 Remove duplicate rows automatically.
📌 Create consolidated reports.
📌 Simplify data retrieval from similar datasets.

📝 Basic Syntax

UNION Syntax

SELECT column1, column2
FROM table1

UNION

SELECT column1, column2
FROM table2;

📌 Rules for Using UNION

Rule	Description
Same Number of Columns	Each SELECT statement must return the same number of columns.
Compatible Data Types	Corresponding columns should have compatible data types.
Column Order	The columns must appear in the same order in every query.
Column Names	The final result uses the column names from the first SELECT statement.

📊 Sample Tables

Consider the following UndergraduateStudents table:

StudentID	Name
101	Alice
102	Bob
103	Charlie

Consider the following PostgraduateStudents table:

StudentID	Name
201	David
202	Emma
103	Charlie

💡 Basic UNION Example

Combine the names of all undergraduate and postgraduate students.

Basic UNION

SELECT Name
FROM UndergraduateStudents

UNION

SELECT Name
FROM PostgraduateStudents;

Result:

Name
Alice
Bob
Charlie
David
Emma

Important

Although Charlie appears in both tables, the UNION operator returns it only once because duplicate rows are removed.

🔄 UNION ALL

Use UNION ALL when you want to keep duplicate rows in the final result.

UNION ALL Example

SELECT Name
FROM UndergraduateStudents

UNION ALL

SELECT Name
FROM PostgraduateStudents;

In this case, Charlie appears twice because UNION ALL does not remove duplicates.

📊 UNION with WHERE

Each SELECT statement can have its own WHERE clause.

UNION with WHERE

SELECT Name
FROM UndergraduateStudents
WHERE StudentID > 101

UNION

SELECT Name
FROM PostgraduateStudents
WHERE StudentID > 201;

📈 UNION with ORDER BY

The ORDER BY clause is written only once, at the end of the complete UNION query.

UNION with ORDER BY

SELECT Name
FROM UndergraduateStudents

UNION

SELECT Name
FROM PostgraduateStudents

ORDER BY Name ASC;

Tip

You cannot place an ORDER BY clause after each individual SELECT statement. Apply it only to the final combined result.

🏷️ Using Aliases with UNION

Column aliases improve the readability of the combined result.

UNION with Column Alias

SELECT Name AS StudentName
FROM UndergraduateStudents

UNION

SELECT Name
FROM PostgraduateStudents;

🔗 UNION Across Different Tables

The tables do not need to have the same name. They only need compatible columns.

Combine Employees and Teachers

SELECT Name,
       City
FROM Employees

UNION

SELECT Name,
       City
FROM Teachers;

⚖️ UNION vs UNION ALL

Feature	UNION	UNION ALL
Duplicate Rows	Removed	Kept
Performance	Slower (duplicate removal)	Faster
Use Case	Unique results	All results including duplicates

💼 Real-World Example

A university wants a single report containing all students from undergraduate and postgraduate programs without duplicate names.

Combined Student Report

SELECT StudentID,
       Name
FROM UndergraduateStudents

UNION

SELECT StudentID,
       Name
FROM PostgraduateStudents

ORDER BY Name;

This query generates one consolidated list of students from both programs.

⚠️ Common Mistakes

❌ Returning a different number of columns in each SELECT.
❌ Using incompatible data types for corresponding columns.
❌ Placing ORDER BY before the final SELECT.
❌ Using UNION when duplicate rows should be preserved.

Warning

If duplicate records are expected and should remain in the result, choose UNION ALL instead of UNION to avoid unnecessary duplicate elimination and improve performance.

⚠️ Best Practices

Best Practice

Ensure that all SELECT statements return the same number of columns with compatible data types. Use UNION for unique results and UNION ALL when duplicates are acceptable. Apply ORDER BY only once at the end of the combined query.

🚀 Key Points to Remember

📌 UNION combines the results of multiple SELECT statements.
📌 Duplicate rows are removed automatically.
📌 All queries must return the same number of columns.
📌 Corresponding columns must have compatible data types.
📌 UNION ALL keeps duplicate rows and is generally faster.
📌 Use ORDER BY only after the final SELECT statement.

>>"The UNION operator brings multiple query results together as one unified dataset."

Summary

✅ The UNION operator is an essential SQL feature for combining the results of multiple queries into a single result set. It automatically removes duplicate rows, making it ideal for creating consolidated reports and merging similar datasets. When duplicates must be preserved, use UNION ALL for better performance and complete results.