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.
