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:
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.