SQL

UNION ALL in SQL

🚀 The UNION ALL operator in SQL is used to combine the results of two or more SELECT statementsinto a single result set without removing duplicate rows. Unlike UNION, every row returned by each query is included in the final result, making UNION ALL faster and more efficient when duplicate elimination is not required.

📖 What is UNION ALL?

The UNION ALL operator merges the results of multiple SELECT statements while preserving every row, including duplicates. It is commonly used when combining data from similar tables or multiple queries where duplicate values are meaningful.

Information

UNION ALL does not check for duplicate rows, which generally makes it faster than UNION.

🎯 Why Use UNION ALL?

Use UNION ALL when every row is important, including duplicate records.

📌 Combine results without removing duplicates.
📌 Improve query performance.
📌 Merge data from multiple sources.
📌 Preserve repeated values for accurate reporting.
📌 Process large datasets efficiently.

📝 Basic Syntax

UNION ALL Syntax

SELECT column1, column2
FROM table1

UNION ALL

SELECT column1, column2
FROM table2;

📌 Rules for Using UNION ALL

Rule	Description
Same Number of Columns	Every 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.
Duplicates	Duplicate rows are preserved.

📊 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 ALL Example

Combine the names of undergraduate and postgraduate students while preserving duplicates.

Basic UNION ALL

SELECT Name
FROM UndergraduateStudents

UNION ALL

SELECT Name
FROM PostgraduateStudents;

Result:

Name
Alice
Bob
Charlie
David
Emma
Charlie

Important

Notice that Charlie appears twice because UNION ALL keeps duplicate rows.

📊 UNION ALL with WHERE

Each SELECT statement can include its own WHERE clause.

UNION ALL with WHERE

SELECT Name
FROM UndergraduateStudents
WHERE StudentID > 101

UNION ALL

SELECT Name
FROM PostgraduateStudents
WHERE StudentID > 201;

📈 UNION ALL with ORDER BY

Use a single ORDER BY clause after the final SELECT statement to sort the combined result.

UNION ALL with ORDER BY

SELECT Name
FROM UndergraduateStudents

UNION ALL

SELECT Name
FROM PostgraduateStudents

ORDER BY Name ASC;

Tip

The ORDER BY clause applies to the entire combined result set, not to individual SELECT statements.

🏷️ Using Aliases with UNION ALL

Column aliases improve the readability of the final output.

UNION ALL with Alias

SELECT Name AS StudentName
FROM UndergraduateStudents

UNION ALL

SELECT Name
FROM PostgraduateStudents;

🔗 UNION ALL Across Different Tables

UNION ALL can combine results from different tables as long as the selected columns are compatible.

Combine Employees and Teachers

SELECT Name,
       City
FROM Employees

UNION ALL

SELECT Name,
       City
FROM Teachers;

⚖️ UNION vs UNION ALL

Feature	UNION	UNION ALL
Duplicate Rows	Removed	Preserved
Performance	Slower	Faster
Duplicate Check	Required	Not Required
Typical Use	Unique results	Complete results

💼 Real-World Example

A university wants to generate a report containing all student registrations from undergraduate and postgraduate programs, including duplicate entries because some students are enrolled in both programs.

Combined Registration Report

SELECT StudentID,
       Name
FROM UndergraduateStudents

UNION ALL

SELECT StudentID,
       Name
FROM PostgraduateStudents

ORDER BY Name;

This query combines every registration record without removing duplicates, ensuring complete reporting.

⚠️ Common Mistakes

❌ Returning different numbers of columns in each SELECT.
❌ Using incompatible data types.
❌ Assuming duplicate rows will be removed automatically.
❌ Placing ORDER BY before the final SELECT.

Warning

If duplicate rows should not appear in the result, use UNION instead of UNION ALL.

⚠️ Best Practices

Best Practice

Use UNION ALL when duplicate rows are acceptable or required. Ensure every SELECT statement returns the same number of columns with compatible data types, and apply ORDER BY only once after the final query. Choose UNION ALL over UNION when performance is important and duplicate elimination is unnecessary.

🚀 Key Points to Remember

📌 UNION ALL combines multiple query results.
📌 Duplicate rows are preserved.
📌 It is generally faster than UNION.
📌 All queries must return the same number of columns.
📌 Corresponding columns must have compatible data types.
📌 Use a single ORDER BY clause at the end of the combined query.

>>"UNION ALL combines everything exactly as it is, preserving every row without filtering duplicates."

Summary

✅ The UNION ALL operator is an efficient SQL feature for combining the results of multiple SELECT statements while keeping every row, including duplicates. It is ideal for large datasets, performance- sensitive applications, and scenarios where repeated records are meaningful.