In SQL Server, the UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The result set of the UNION operation contains distinct rows that are present in either of the SELECT statements.

Syntax

The syntax of the UNION operator in SQL Server is as follows:

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

Here, the column list and table names in the SELECT statements should be identical, and the data types of the columns should also be compatible.

By default, the UNION operator removes duplicate rows from the result set. If you want to include all the rows, including duplicates, you can use the UNION ALL operator instead.

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

It's important to note that the column names in the result set of a UNION operation are determined by the column names in the first SELECT statement. Therefore, it's recommended to use the same column names in all the SELECT statements, to avoid any confusion in the result set.

Also, the number of columns and their data types in all the SELECT statements should be the same, otherwise, you'll get an error.

In summary, the UNION operator in SQL Server is a useful tool for combining the results of multiple SELECT statements into a single result set. It allows you to retrieve data from different tables or views and present it in a consolidated manner.