Difference between Union and Union All in SQL Server

In SQL Server, both UNION and UNION ALL are used to combine the results of two or more SELECT queries. However, there are some key differences between them:

1. UNION:

   • The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.
   • It removes duplicate rows from the final result set.
   • Columns in the SELECT statements must match in number and data type.

   Example:

UNION: Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

SELECT column1, column2 FROM table1

UNION

SELECT column1, column2 FROM table2;

SELECT JOB FROM EMP WHERE DEPTNO = 10

UNION

SELECT JOB FROM EMP WHERE DEPTNO = 30

2. UNION ALL:

• The UNION ALL operator is also used to combine the result sets of two or more SELECT statements into a single result set.
• Unlike UNION, it does not remove duplicate rows. It simply appends all rows from the second SELECT statement to the result set of the first SELECT statement.
• Columns in the SELECT statements must match in number and data type.

Example:

UNION ALL: These is same as UNION but in this case duplicates will not be eliminated.

SELECT column1, column2 FROM table1

UNION ALL

SELECT column1, column2 FROM table2;

SELECT JOB FROM EMP WHERE DEPTNO = 10

UNION ALL

SELECT JOB FROM EMP WHERE DEPTNO = 30

In summary, if you want to include duplicate rows in the result set, you can use UNION ALL. If you want to remove duplicates, you can use UNION.