A Complete Guide - SQL Server UNION and INTERSECT

SQL Server UNION and INTERSECT: Detailed Explanation and Important Information

In SQL Server, the UNION and INTERSECT operators are set-based operations used to combine results from multiple SELECT queries into a single result set. These operators help in merging datasets while applying specific rules to eliminate duplicates or retain only common rows.

UNION Operator

• Purpose: The UNION operator combines the result sets of two or more SELECT statements into a single result set.

• Syntax:

  SELECT column1, column2, ...
  FROM table1
  UNION [ALL]
  SELECT column1, column2, ...
  FROM table2;
  

  • UNION: Removes duplicate rows from the final result set.
  • UNION ALL: Includes all rows from the result sets including duplicates.

• Important Points:

  • Matching Columns: Both SELECT statements must have the same number of columns, and corresponding columns must have compatible data types.
  • Column Aliases: Column aliases should be provided in the first SELECT statement. They will apply to the entire result set.
  • Ordering: The ORDER BY clause can only appear after the last SELECT in a UNION statement when using UNION ALL or UNION.

Detailed Example Using UNION

Consider two tables, Employees_A and Employees_B, with the following data:

-- Employees_A table
CREATE TABLE Employees_A
(
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(150),
    Department NVARCHAR(50)
);

INSERT INTO Employees_A VALUES (1, 'John Doe', 'Sales'), (2, 'Jane Smith', 'Finance');

-- Employees_B table
CREATE TABLE Employees_B
(
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(150),
    Department NVARCHAR(50)
);

INSERT INTO Employees_B VALUES (2, 'Jane Smith', 'HR'), (3, 'Alice Brown', 'Marketing');

Performing a UNION ALL:

SELECT EmployeeID, EmployeeName, Department 
FROM Employees_A
UNION ALL
SELECT EmployeeID, EmployeeName, Department
FROM Employees_B;

Result:

EmployeeID | EmployeeName | Department
--------------------------------------
1          | John Doe     | Sales
2          | Jane Smith   | Finance
2          | Jane Smith   | HR
3          | Alice Brown  | Marketing

Without ALL, performing UNION:

SELECT EmployeeID, EmployeeName, Department
FROM Employees_A
UNION
SELECT EmployeeID, EmployeeName, Department
FROM Employees_B;

Result:

EmployeeID | EmployeeName | Department
--------------------------------------
1          | John Doe     | Sales
2          | Jane Smith   | Finance
3          | Alice Brown  | Marketing
2          | Jane Smith   | HR

Here, the result includes all unique EmployeeID entries. However, because the combination of EmployeeID 2 and "Jane Smith" is not completely identical (i.e., different departments), both rows are retained. Note that UNION automatically orders the combined result set in ascending order by default.

INTERSECT Operator

• Purpose: The INTERSECT operator returns distinct rows that are common between the result sets of two SELECT statements.

• Syntax:

  SELECT column1, column2, ...
  FROM table1
  INTERSECT
  SELECT column1, column2, ...
  FROM table2;
  

• Important Points:

  • Similar to UNION, INTERSECT requires the same number and type of columns across all SELECT statements involved.
  • INTERSECT automatically removes duplicate rows and ensures that only unique matching rows are returned.
  • The ORDER BY clause can only appear once at the end of the entire INTERSECT operation.

Example Using INTERSECT

Continuing from the previous example:

SELECT EmployeeID, EmployeeName
FROM Employees_A
INTERSECT
SELECT EmployeeID, EmployeeName
FROM Employees_B;

Result:

EmployeeID | EmployeeName
-------------------------
2          | Jane Smith

This shows only the employees who exist in both Employees_A and Employees_B. Note how the Department column is not included; it's necessary to include only those columns which have identical definitions in all SELECT statements.

Key Considerations and Best Practices

1. Data Types Compatibility: Ensure that columns selected in each SELECT statement are of compatible data types.

2. DISTINCT Behavior: Remember that UNION by default performs a DISTINCT on the combined result, whereas INTERSECT inherently provides distinct matches.

3. Performance: Be mindful of performance, especially when dealing with large datasets, as these set-based operations may involve sorting and elimination of duplicates.

4. Null Handling: UNION and INTERSECT treat two NULLs as equal; thus, they may return rows that are considered duplicates based on NULL values.

5. Sorting: Sorting applies across the entire result set only if an ORDER BY clause is specified at the end of the query.

6. Use Cases:

   • UNION: Useful when you need to combine data from multiple tables but ensure no duplicated entries.
   • INTERSECT: Ideal for scenarios where you want to find records common to two or more tables, such as shared customers or overlapping projects.

7. Index and Statistics: Proper use of indexes and up-to-date statistics can significantly improve performance of UNION and INTERSECT operations.