UNION Operator in MSSQL

In MSSQL Server, the UNION operator is used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows between the various SELECT statements. Here's how you can use the UNION operator:

SELECT column1, column2, ...

FROM table1

WHERE condition1

UNION

SELECT column1, column2, ...

FROM table2

WHERE condition2;

This will combine the results of the two SELECT statements into a single result set, removing any duplicate rows. 

Here are some key points to note about the UNION operator in SQL Server:

1. The number and order of columns in all SELECT statements must be the same.

2. By default, UNION removes duplicate rows. If you want to include duplicate rows, you can use UNION ALL.

3. Column names in the result set are determined by the column names in the first SELECT statement.

4. The data types of corresponding columns from different SELECT statements must be compatible, otherwise, an error will occur.

Example:

SELECT product_id, product_name

FROM products

WHERE category = 'Electronics'

UNION

SELECT product_id, product_name

FROM products

WHERE category = 'Clothing';

This will retrieve the product ID and product name of products in the Electronics category and Clothing category, combining the results into a single result set.

Here's an example of using the UNION operator in Microsoft SQL Server with sample output:-

Consider two tables, Students1 and Students2, with similar structures:-

CREATE TABLE Students1 (

    StudentID INT,

    Name NVARCHAR(50),

    Grade INT

);

CREATE TABLE Students2 (

    StudentID INT,

    Name NVARCHAR(50),

    Grade INT

);

INSERT INTO Students1 (StudentID, Name, Grade)

VALUES (1, 'Alice', 85),

       (2, 'Bob', 92),

       (3, 'Charlie', 78);

INSERT INTO Students2 (StudentID, Name, Grade)

VALUES (4, 'David', 88),

       (5, 'Emily', 95),

       (1, 'Alice', 90);

Now, let's use the UNION operator to combine the results of selecting all students from both tables:

SELECT StudentID, Name, Grade

FROM Students1

UNION

SELECT StudentID, Name, Grade

FROM Students2;

Output:

StudentID   Name      Grade

----------------------------

1           Alice     85

1           Alice     90

2           Bob       92

3           Charlie   78

4           David     88

5           Emily     95

In this example:

- The UNION operator combines the results of selecting all students from both Students1 and Students2.

- Duplicate rows are automatically removed.

- The resulting output contains a combined list of students from both tables.

Here are five frequently asked questions about the UNION operator in Microsoft SQL Server:-

1. What is the UNION operator used for in SQL Server?

   - The UNION operator is used to combine the results of multiple SELECT statements into a single result set. It removes duplicate rows by default.

2. What is the difference between UNION and UNION ALL?

   - The UNION operator removes duplicate rows from the combined result set, while UNION ALL includes all rows from all SELECT statements, including duplicates.

3. Can I use UNION to combine SELECT statements with a different number of columns?

   - No, all SELECT statements in a UNION operation must have the same number of columns, and the corresponding columns must have compatible data types.

4. How are column names determined in the result set of a UNION operation?

   - The column names in the result set are determined by the column names in the first SELECT statement. Subsequent SELECT statements must have the same column names.

5. Does the order of rows matter when using UNION?

   - No, the UNION operator combines the results of SELECT statements and returns a single result set, where the order of rows is not guaranteed unless explicitly specified using an ORDER BY clause.