UNION vs UNION ALL in SQL SERVER: A Comparative Analysis of Differences and Uses

Union vs Union All: The UNION and UNION ALL operators are most commonly used to retrieve records from multiple tables into a single result set.

UNION returns distinct records from both tables, while UNION ALL returns all the records, including duplicates.

In this article, we will look at the differences between UNION and UNION ALL. So before making a comparison, let’s briefly discuss these operators.

UNION-VS-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

  • UNION can only be used to combine data from tables with the same number of columns and data types.
  • The SELECT statement used before UNION must match the number of columns and data types of the SELECT statement used after UNION.
  • UNION only returns distinct values, any duplicate rows will be removed in the final result set.
  • The order of the columns in the SELECT statement does not matter when using UNION.
  • UNION can be used with other SQL clauses, such as WHERE and ORDER BY, to further filter and sort the data.

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 includes all rows, including duplicates.

It is important to note that it cannot guarantee the order of rows in the result set when using UNION ALL. 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

  • We can only use UNION to combine data from tables with the same number of columns and data types.
  • The SELECT statement used before UNION must match the number of columns and data types of the SELECT statement used after UNION.
  • UNION only returns distinct values and will remove any duplicate rows in the final result set.
  • The order of the columns in the SELECT statement does not matter when using UNION.
  • We can use UNION with other SQL clauses, such as WHERE and ORDER BY, to filter and sort the data further.

What are the main differences between UNION and UNION ALL in SQL Server?

  • UNION removes duplicates, whereas UNION ALL does not.
  •  UNION requires the number and order of columns to be the same in all SELECT statements, whereas UNION ALL does not.
  •  UNION requires the data types of columns to be compatible across all SELECT statements, while UNION ALL does not.
  •  UNION is slower than UNION ALL in SQL Server because it requires additional 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

IdNameSalary
1Sadaf45000
2Shekh Ali95000
3Sara65000
4Rohit75000
5Amy85000

Table2

IdNameSalary
1Sadaf45000
2Shekh Ali95000
6David65000
7Steve75000
8Roman85000

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:

IdNameSalary
1Sadaf45000
2Shekh Ali95000
3Sara65000
4Rohit75000
5Amy85000
6David65000
7Steve75000
8Roman85000

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:

IdNameSalary
1Sadaf45000
2Shekh Ali95000

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:

IdNameSalary
1Sadaf45000
2Shekh Ali95000
3Sara65000
4Rohit75000
5Amy85000
1Sadaf45000
2Shekh Ali95000
6David65000
7Steve75000
8Roman85000

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

Q: What is the main difference between UNION and UNION ALL in SQL Server?

The main difference between UNION and UNION ALL in SQL Server is that UNION removes duplicate rows and UNION ALL includes all rows, including duplicates.

Q: When should you use UNION vs UNION ALL?

You can use UNION when you want to remove duplicate records and only see unique results. Use UNION ALL when you want to include all rows, even if they are duplicates.

Q: Is it necessary for UNION to have the same number and order of columns in all SELECT statements?

Yes, UNION requires the number and order of columns to be the same in all SELECT statements whereas UNION ALL does not have this requirement.

Q: Does UNION require the data types of columns to be compatible across all SELECT statements?

Yes, UNION requires the data types of columns to be compatible across all SELECT statements whereas UNION ALL does not have this requirement.

Q: Is UNION slower than UNION ALL in SQL Server?

Yes, UNION is slower than UNION ALL in SQL Server because it requires additional processing to remove duplicate rows.

Q: Can we use UNION and UNION ALL in a subquery?

Yes, both UNION and UNION ALL can be used in a subquery.

Q: Does UNION or UNION ALL keep the original ordering of the rows?

No, UNION and UNION ALL do not keep the original ordering of the rows. If you need to maintain the original ordering, you should use the ORDER BY clause in your SELECT statement.

References: Wikipedia-Set operations (SQL)

Articles you might also like:

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

c20e93cf99b4a0eb1e4a099de6c2c300?s=250&r=g
5 1 vote
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments