Difference Between UNION and UNION ALL in SQL

Last updated on July 9th, 2023 at 07:11 am

When comparing UNION with UNION ALL, there is a significant difference:

• UNION returns only unique records, while UNION ALL returns all records, including duplicates.
• UNION performs additional processing to identify and eliminate duplicate rows, which can make it slower compared to UNION ALL.

In this article, we will look at the differences between UNION and UNION ALL in SQL with multiple examples.

Comparison of UNION and UNION ALL in SQL Server:

What is the UNION operator?

The UNION operator is used to combine the results of two or more SELECT statements into a single result set of all matching rows. The resulting set includes only unique rows, meaning any duplicates are removed.

Note: The two queries must have the same number of columns and compatible data types in order to combine the data in a single result set.

Syntax:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

Fundamental rules for using UNION in SQL Server

• Tables being combined with UNION must have the same number of columns and data types.
• The SELECT statements before and after UNION must have the same number of columns and data types.
• UNION removes duplicate rows and returns only distinct values in the final result set.
• The order of columns in the SELECT statement doesn’t matter when using UNION.
• We can use UNION with other SQL clauses like WHERE and ORDER BY to filter and sort the data further.

What is the UNION ALL operator in SQL Server?

In SQL Server, the UNION ALL operator is also used to combine the results of two or more SELECT statements, but it returns all rows including duplicates.

It’s worth mentioning that UNION ALL does not guarantee the order of rows in the result set. The rows from the second table may be intermingled or appear before or after the rows from the first table. To ensure a specific order, we must use the ORDER BY clause.

Syntax:

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2

Fundamental rules for using UNION ALL in SQL Server

• UNION ALL combines the results of two or more SELECT statements.
• It retrieves all the records from each SELECT statement without removing duplicates.
• The number and order of columns in all SELECT statements must be the same.
• Data types of corresponding columns in each SELECT statement must be compatible.
• The result set of UNION ALL retains the order in which the SELECT statements are specified.
• UNION ALL is typically faster than UNION since it does not require duplicate elimination.

UNION VS. UNION ALL in SQL Server

1. UNION removes duplicates, while UNION ALL keeps all records, including duplicates.
2. UNION requires the same number and order of columns in all SELECT statements, while UNION ALL doesn’t.
3. UNION requires compatible data types for columns in all SELECT statements, while UNION ALL doesn’t have this requirement.
4. UNION is slower in SQL Server because it needs extra processing to remove duplicate records.

When should you use UNION vs UNION ALL in SQL?

• Use UNION when you want to remove duplicates and only see unique results.
•  Use UNION ALL when you want to include all rows, even if they are duplicates.
•  Use UNION to ensure that the number and order of columns are the same across all SELECT statements.
•  Use UNION ALL when you don’t care about duplicates and don’t need to ensure the number and order of columns are the same.

In conclusion, UNION and UNION ALL are both powerful operators for combining data sets in SQL Server. However, it’s important to understand the differences and choose the operator that best fits your needs.

Example: Union vs Union All in SQL

Here is an example of using both the UNION and UNION ALL operators on two sample tables, Table1 and Table2.

-- Create TABLE1 and TABLE2
CREATE TABLE Table1 (ID INT, Name VARCHAR(255), Salary INT);
CREATE TABLE Table2 (ID INT, Name VARCHAR(255), Salary INT);

-- Insert some records into the 'Table1' table
INSERT INTO Table1 (ID, Name, Salary) VALUES (1, 'Sadaf', 45000);
INSERT INTO Table1 (ID, Name, Salary) VALUES (2, 'Shekh Ali', 95000);
INSERT INTO Table1 (ID, Name, Salary) VALUES (3, 'Sara', 65000);
INSERT INTO Table1 (ID, Name, Salary) VALUES (4, 'Rohit', 75000);
INSERT INTO Table1 (ID, Name, Salary) VALUES (5, 'Amy', 85000);

-- Insert some records into the 'Table2' table
INSERT INTO Table2 (ID, Name, Salary) VALUES (1, 'Sadaf', 45000);
INSERT INTO Table2 (ID, Name, Salary) VALUES (2, 'Shekh Ali', 95000);
INSERT INTO Table2 (ID, Name, Salary) VALUES (6, 'David', 65000);
INSERT INTO Table2 (ID, Name, Salary) VALUES (7, 'Steve', 75000);
INSERT INTO Table2 (ID, Name, Salary) VALUES (8, 'Roman', 85000);

Table1

Table2

Example: UNION Operator

The following is an example of using the UNION operator to combine the results from the two tables and remove duplicates:

SELECT ID, Name, Salary FROM Table1
UNION
SELECT ID, Name, Salary FROM Table2

The result of this query would be like this:

In the example provided above, the records that are considered duplicates are the ones that have the same values in all columns, ID, Name, and Salary.
The records that are considered as duplicates when using the UNION operator are:

These records are present in both Table1 and Table2 and have the same values in all columns, so they are considered as duplicates and removed in the final result set when using the UNION operator.

But when using UNION ALL operator, it doesn’t remove any duplicates and returns all the rows present in both Table1 and Table2.

Example: UNION ALL Operator

The following is an example of using the UNION ALL operator to combine the results of the two tables and include duplicates:

SELECT ID, Name, Salary FROM Table1
UNION ALL
SELECT ID, Name, Salary FROM Table2

The result of this query would be like this:

As you can see, the above query returns all the rows from both Table1 and Table2, including the duplicate rows with ID 1 and 2, Name ‘Sadaf’ and ‘Shekh Ali’, and Salary 45000 and 95000, respectively.

In the UNION ALL operator duplicate records are not removed, so all the rows are returned in the final result set. So it returns all the rows present in both Table1 and Table2, including the duplicate rows.

FAQs

References: Wikipedia-Set operations (SQL)

Articles you might also like:

• Delete vs Truncate vs Drop in SQL Server: What is the Difference between DELETE, TRUNCATE and DROP in SQL?
• Having VS Where Clause in SQL | Explore the main Differences between Where and Having Clause in SQL
• How to Create Foreign Key in SQL: Best Practices and Examples
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• Stored Procedure in SQL Server With Examples
• Create, Alter, and Drop Database In SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples
• SQL pivot tables: Understanding pivot tables in SQL Server

Please share this post and let us know what you think in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024