In SQL Server, there are two operators that allow us to combine the result sets of multiple SELECT statements: UNION and UNION ALL. While both operators serve a similar purpose, there are some key differences between them that we need to be aware of.

SQL Union Operator

The SQL UNION operator is used to combine the result sets of two or more SELECT statements. It returns a single result set that contains unique rows from all the SELECT statements.

The syntax for the SQL UNION operator is as follows:

SELECT column1, column2, ..., columnN
FROM table1
UNION
SELECT column1, column2, ..., columnN
FROM table2;

When using the UNION operator, there are a few points to keep in mind:

• Both SELECT statements must have the same number of columns.
• The columns in both SELECT statements must have compatible data types.
• The column order must match in both SELECT statements.
• We can define GROUP BY and HAVING clauses with each SELECT statement, but they cannot be used with the result set.
• We cannot use the ORDER BY clause with individual SELECT statements. It can only be used with the result set generated from the UNION of both SELECT statements.

Let’s consider an example to understand the SQL UNION operator:

CREATE TABLE TableA (ID INT);
INSERT INTO TableA VALUES (1), (2), (3), (4);

CREATE TABLE TableB (ID INT);
INSERT INTO TableB VALUES (3), (4), (5), (6);

SELECT ID FROM TableA
UNION
SELECT ID FROM TableB;

The output of this query will be: 1, 2, 3, 4, 5, 6. As you can see, the duplicate values (3 and 4) are removed, and only one row is returned for each duplicate value. The UNION operator performs a DISTINCT operation across all columns in the result set.

SQL Union All Operator

The SQL UNION ALL operator also combines the result sets of two or more SELECT statements, but it does not remove any duplicate rows from the output. It returns all rows from all SELECT statements.

The syntax for the SQL UNION ALL operator is as follows:

SELECT column1, column2, ..., columnN
FROM table1
UNION ALL
SELECT column1, column2, ..., columnN
FROM table2;

When using the UNION ALL operator, there are no restrictions on the number of columns, data types, or column order between the SELECT statements.

Let’s consider the same example as before, but this time using the UNION ALL operator:

SELECT ID FROM TableA
UNION ALL
SELECT ID FROM TableB;

The output of this query will be: 1, 2, 3, 4, 3, 4, 5, 6. As you can see, all rows from both tables are returned, including the duplicate values.

Performance Considerations

In terms of performance, the SQL UNION ALL operator is generally faster than the SQL UNION operator. This is because the UNION operator performs a DISTINCT operation, which can be more expensive, especially when working with large data sets.

Order By Clause

When using the UNION operator, we can only use the ORDER BY clause with the final result set. We cannot use it with individual SELECT statements. Attempting to use the ORDER BY clause with individual SELECT statements will result in a syntax error.

Combining Union and Union All

We can also use both the UNION and UNION ALL operators in a single SELECT statement. For example, we can perform a UNION between two tables and then perform a UNION ALL between the result set and another table.

SELECT column1, column2, ..., columnN
FROM table1
UNION ALL
(
    SELECT column1, column2, ..., columnN
    FROM table2
    UNION
    SELECT column1, column2, ..., columnN
    FROM table3
);

This allows us to create more complex queries and combine multiple result sets in a single statement.

Conclusion

In this article, we have explored the SQL UNION and UNION ALL operators in SQL Server. We have learned about their differences and use cases. The UNION operator combines the result sets of multiple SELECT statements and removes duplicate rows, while the UNION ALL operator combines the result sets without removing duplicates. Understanding these operators and their behavior can help us write more efficient and effective SQL queries.

I hope you found this article helpful. If you have any feedback or questions, please leave a comment below.