SQL Server UNION and INTERSECT Step by step Implementation and Top 10 Questions and Answers
 Last Update: April 01, 2025      16 mins read      Difficulty-Level: beginner

SQL Server UNION and INTERSECT: Detailed Explanation and Important Information

SQL Server, a powerful relational database management system (RDBMS) developed by Microsoft, provides several set operations that allow us to combine the results of two or more SELECT statements. Among these operations are UNION and INTERSECT. Understanding these operations is crucial for manipulating and retrieving data efficiently from multiple tables or queries. This article aims to provide a comprehensive explanation of both UNION and INTERSECT in SQL Server, along with their syntax, use cases, and important considerations.

Understanding UNION

Definition: 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 between the various SELECT statements.

Syntax:

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

There are two types of UNION operations:

  • UNION: Removes duplicate rows.
  • UNION ALL: Includes all rows, even if there are duplicates.

Example with UNION:

SELECT ProductID, ProductName
FROM ProductA
UNION
SELECT ProductID, ProductName
FROM ProductB;

Example with UNION ALL:

SELECT ProductID, ProductName
FROM ProductA
UNION ALL
SELECT ProductID, ProductName
FROM ProductB;

In the above examples:

  • UNION will return a list of unique products (ProductID and ProductName) from both ProductA and ProductB.
  • UNION ALL will return all products, including duplicates from both tables.

Important Points:

  • Same Number of Columns: Both UNION and UNION ALL require that the number of columns selected in each SELECT statement is the same.
  • Compatible Data Types: The data types of the columns must be compatible or convertible to a common type.
  • Sorting: If you want to sort the combined result set, the ORDER BY clause must be specified at the end of the last SELECT statement.
  • Performance Considerations: UNION ALL is generally faster than UNION because it does not need to perform the additional step of removing duplicates.

Understanding INTERSECT

Definition: The INTERSECT operator is used to return only the rows that are common between the result sets of two or more SELECT statements.

Syntax:

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

Example:

SELECT ProductID, ProductName
FROM ProductA
INTERSECT
SELECT ProductID, ProductName
FROM ProductB;

In the above example, the query will return a list of products that exist in both ProductA and ProductB, based on the ProductID and ProductName columns.

Important Points:

  • Data Type Compatibility: Similar to UNION, the data types of the columns in each SELECT statement must be compatible or convertible to a common type.
  • Same Number of Columns: The number of columns and their order should match between all SELECT statements.
  • Duplicates: By default, INTERSECT removes duplicates from the resulting set.
  • Performance: INTERSECT can be less efficient than UNION or UNION ALL because it needs to find overlapping rows between two result sets, which can involve more complex operations and data comparisons.

Use Cases

UNION:

  1. Combining Results: When you need to combine results from multiple tables or queries into a single result set and ensure that there are no duplicate rows.
  2. Data Consolidation: To consolidate data from different sources that have the same schema into a unified view.
  3. Reporting: For generating reports that require data from multiple tables.

INTERSECT:

  1. Finding Common Data: To identify common records between multiple tables, useful in data validation or matching processes.
  2. Auditing: Useful in auditing scenarios where you need to check consistency across different data sources.
  3. Data Comparison: To compare data between two or more tables and find matches, useful in deduplication processes.

Important Considerations

  1. Performance: Be mindful of the performance implications of UNION, UNION ALL, and INTERSECT. Complex joins or large datasets can impact the execution time significantly.
  2. Sorting: Use ORDER BY only after the last SELECT statement to sort the combined result set.
  3. Data Types: Ensure that data types are compatible to avoid errors during the execution of these set operations.
  4. Indexing: Indexing columns that are part of the SELECT statements can improve the performance of these operations.

Conclusion

In SQL Server, UNION and INTERSECT are powerful tools for combining and comparing result sets from multiple SELECT statements. Understanding their differences, syntax, use cases, and performance implications is essential for effective data manipulation and retrieval. By leveraging these operations, developers and database administrators can efficiently manage and analyze data across various tables and queries, leading to more informed decision-making and data-driven applications.

Examples, Set Route and Run Application then Data Flow Step-by-Step for Beginners: SQL Server UNION and INTERSECT

Introduction

SQL Server is a powerful relational database management system that supports a variety of operations for querying and manipulating data. Two fundamental operations in SQL Server that are often used are UNION and INTERSECT. These set operators allow you to combine the results of two or more SELECT statements into a single result set, with UNION displaying unique rows from both datasets and INTERSECT showing only the rows that are common to both.

In this guide, we will walk through setting up a scenario, using UNION and INTERSECT, running the application, and tracing the data flow step-by-step. This will provide a comprehensive, beginner-friendly example to help you understand how these operations work.

Step 1: Setting Up the Environment

Before diving into UNION and INTERSECT, let's set up a simple database environment.

  1. Open SQL Server Management Studio (SSMS): This is the main tool for managing SQL Server. You can download and install it from Microsoft's official website.

  2. Create a SQL Server Database:

    • In SSMS, right-click on 'Databases' under your server instance and select 'New Database'.
    • Name your database SetOperationsDemo.

Step 2: Creating Sample Tables

For demonstration purposes, let's create two tables within our database.

  1. Create the First Table - Employees:

    USE SetOperationsDemo;
GO

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);

INSERT INTO Employees (EmployeeID, Name, Department) VALUES
(1, 'Alice Johnson', 'HR'),
(2, 'Bob Smith', 'IT'),
(3, 'Charlie Brown', 'Finance'),
(4, 'David Wilson', 'IT');

  2. Create the Second Table - Contractors:

    CREATE TABLE Contractors (
    ContractorID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);

INSERT INTO Contractors (ContractorID, Name, Department) VALUES
(101, 'Eve Davis', 'IT'),
(102, 'Franklin Stone', 'Marketing'),
(103, 'Grace Lee', 'IT'),
(104, 'Hannah Lee', 'Finance');

Step 3: Using UNION to Combine Data

The UNION operator combines the results of two or more SELECT statements and returns distinct rows (i.e., no duplicates).

  1. Write a UNION Query:

    SELECT Name, Department FROM Employees
UNION
SELECT Name, Department FROM Contractors;

  2. Execute the Query:

    • Click on the 'Execute' button or press F5 to run the query.
    • The result will include all unique names and departments from both Employees and Contractors.

Step 4: Using INTERSECT to Find Common Data

The INTERSECT operator returns only the rows that are common between the results of two or more SELECT statements.

  1. Write an INTERSECT Query:

    SELECT Department FROM Employees
INTERSECT
SELECT Department FROM Contractors;

  2. Execute the Query:

    • Click on the 'Execute' button or press F5 to run the query.
    • The result will include only the departments that appear in both Employees and Contractors, which in this case is 'Finance' and 'IT'.

Step 5: Understanding Data Flow

Now that we have executed the queries, let’s understand the data flow:

  1. UNION Data Flow:

    • SQL Server first retrieves data from both Employees and Contractors tables based on the SELECT statements in the UNION query.
    • It then filters out any duplicate rows, ensuring that the final result set contains only unique combinations of Name and Department.

  2. INTERSECT Data Flow:

    • SQL Server gathers the data from both Employees and Contractors tables based on the SELECT statements in the INTERSECT query.
    • It then compares these datasets to find rows that are common in both. Only those common rows are included in the final result set.

Step 6: Handling ORDER BY

When using UNION or INTERSECT, the ORDER BY clause can be applied only to the last SELECT statement in the combined query, not to individual ones.

  1. Applying ORDER BY:

    SELECT Name, Department FROM Employees
UNION
SELECT Name, Department FROM Contractors
ORDER BY Department;

  2. Execute the Query:

    • Click the 'Execute' button or press F5.
    • The result will be sorted by Department, and all unique rows will be displayed.

Step 7: Including Duplicates with UNION ALL

If you want to include duplicate rows in your combined result set, use UNION ALL instead of UNION.

  1. Write a UNION ALL Query:

    SELECT Name, Department FROM Employees
UNION ALL
SELECT Name, Department FROM Contractors;

  2. Execute the Query:

    • Run the query, and the result will include all rows from both tables, including duplicates.

Conclusion

Through this step-by-step guide, you have learned how to set up a sample environment, create tables, and use the UNION and INTERSECT operators in SQL Server. By understanding data flow and execution steps, you can better manage and query your data effectively, making your applications more robust and efficient. Practice these operations with different datasets to deepen your understanding and expertise in SQL Server.

Top 10 Questions and Answers about SQL Server UNION and INTERSECT

1. What is the purpose of UNION and INTERSECT in SQL Server?

Answer: In SQL Server, UNION and INTERSECT are set operators used to combine the results of two or more SELECT statements into a single result set.

  • UNION: Combines the results from two or more SELECT statements into a single result set that contains all distinct rows from the query inputs. If you use UNION ALL, it will include duplicate rows.

  • INTERSECT: Generates a result set containing only the distinct rows that are common to both SELECT statements.

Example:

-- UNION Example
SELECT EmployeeID, Name FROM Employees_Dept1
UNION
SELECT EmployeeID, Name FROM Employees_Dept2;

-- INTERSECT Example
SELECT EmployeeID, Name FROM Employees_Dept1
INTERSECT
SELECT EmployeeID, Name FROM Employees_Dept2;

2. What are the rules for using UNION and INTERSECT?

Answer: To use UNION or INTERSECT, certain rules must be adhered to:

  1. Number of Columns: The SELECT statements must have the same number of columns.
  2. Compatibility of Datatypes: Corresponding columns in each SELECT statement must have compatible data types.
  3. Column Names: The column names for the result set are taken from the first SELECT statement; subsequent column names in the other queries are ignored.

Example:

-- This will throw an error because of different number of columns
SELECT EmployeeID, Name FROM Employees_Dept1
UNION
SELECT EmployeeID, Name, Department FROM Employees_Dept2;

3. Can UNION or INTERSECT be used with columns of different data types?

Answer: No, UNION or INTERSECT cannot be used directly with columns of different data types, even if they can be implicitly converted. SQL Server requires that the data types in corresponding columns be either identical or implicitly convertible.

Example:

-- This will work because VARCHAR(n) is implicitly convertible to VARCHAR(m)
SELECT EmployeeID, Name FROM Employees_Dept1
UNION
SELECT CAST(EmployeeID AS VARCHAR(10)), Name FROM Employees_Dept2;

-- This will fail because INT and VARCHAR are not implicitly convertible
SELECT EmployeeID, Name FROM Employees_Dept1
UNION
SELECT Name, Name FROM Employees_Dept2;

4. How do UNION and INTERSECT handle duplicate rows?

Answer:

  • UNION: By default, UNION removes duplicate rows from the final result set. To include all rows, including duplicates, use UNION ALL.
  • INTERSECT: Always removes duplicate rows in the result set, as it only returns unique rows that are common between both queries.

Example:

-- UNION Example with duplicates
SELECT EmployeeID FROM Employees_Dept1
UNION ALL
SELECT EmployeeID FROM Employees_Dept2;

-- INTERSECT Example with duplicates
SELECT EmployeeID FROM Employees_Dept1
INTERSECT
SELECT EmployeeID FROM Employees_Dept2;

5. Can UNION or INTERSECT be used in subqueries?

Answer: Yes, UNION and INTERSECT can be used in subqueries. They can be nested within larger queries to return results that are needed as part of the final query logic.

Example:

SELECT Name
FROM (SELECT Name FROM Customers
      UNION
      SELECT Name FROM Suppliers) AS CombinedNames
WHERE LEFT(Name, 1) = 'J';

6. How do UNION and INTERSECT affect performance?

Answer: Using UNION or INTERSECT can impact performance, particularly with large datasets. Here are some considerations:

  • Sorting and Distinct Operations: Both UNION and INTERSECT operations require sorting to remove duplicates, which can be resource-intensive.
  • Avoid Unnecessary Operations: Use UNION ALL if duplicates are not a concern, as it eliminates the need for sorting and thus improves performance.

Example:

-- Optimized with UNION ALL
SELECT EmployeeID FROM Employees_Dept1
UNION ALL
SELECT EmployeeID FROM Employees_Dept2;

7. Can UNION or INTERSECT be used across different databases or tables with different schemas?

Answer: Yes, UNION and INTERSECT can be used across different databases or tables with different schemas, provided the selected columns have compatible data types and the same number of columns.

Example:

-- UNION across different databases
SELECT EmployeeID, Name FROM Database1.dbo.Employees
UNION
SELECT EmployeeID, Name FROM Database2.dbo.Employees;

8. What are some use cases for UNION and INTERSECT?

Answer:

  • UNION: Merging data from multiple sources to provide a consolidated view. This is useful in scenarios like generating a combined list of employees from multiple departments.

  • INTERSECT: Finding common elements across different sources, such as identifying customers who have made purchases in both the current and previous year.

Example:

-- Use Case for UNION
SELECT ProductID, Quantity, 'Dept1' AS Department FROM Sales_Dept1
UNION
SELECT ProductID, Quantity, 'Dept2' AS Department FROM Sales_Dept2;

-- Use Case for INTERSECT
SELECT CustomerID FROM Orders_2020
INTERSECT
SELECT CustomerID FROM Orders_2021;

9. Can UNION or INTERSECT be combined with other set operators?

Answer: Yes, UNION, UNION ALL, and INTERSECT can be combined, but you need to use parentheses to control the order of operations. SQL Server processes INTERSECT before UNION.

Example:

-- Combining UNION and INTERSECT
SELECT EmployeeID FROM Employees_Dept1 INTERSECT SELECT EmployeeID FROM Employees_Dept2
UNION ALL
SELECT EmployeeID FROM Employees_Dept3;

10. How do you handle NULL values in UNION and INTERSECT operations?

Answer:

  • UNION: NULL values are treated as equal, so duplicates will be removed if they exist. NULLs are also considered when determining distinct values.
  • INTERSECT: Two NULL values are considered equal and will be included in the result if they are common to both result sets.

Example:

-- Handling NULL with UNION
SELECT EmployeeID, Name FROM Employees_Dept1 WHERE ManagerID IS NULL
UNION
SELECT EmployeeID, Name FROM Employees_Dept2 WHERE ManagerID IS NULL;

-- Handling NULL with INTERSECT
SELECT DepartmentID, NULL AS ManagerName FROM Departments
INTERSECT
SELECT DepartmentID, NULL AS ManagerName FROM Department_Head;

By understanding these concepts and answering these questions, you can effectively use UNION and INTERSECT in SQL Server to manage and analyze data efficiently.