UNION, UNION All, INTERSECT, EXCEPT used in SQL Server

UNION : 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 from the…

Author

SQLDataDev Editorial Team

Mar 19, 2026 2 min read

UNION: 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 from the result set.
UNION ALL: The UNION ALL operator is similar to UNION, but it includes all rows from the result sets, including duplicates.
INTERSECT: The INTERSECT operator is used to return the common rows that appear in two SELECT statements.
EXCEPT/ MINUS: The EXCEPT (or MINUS in some databases) operator is used to return all rows from the first SELECT statement that are not present in the result of the second SELECT statement.

-- Create @Employee table
DECLARE @Employee TABLE (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50)
);

-- Insert dummy data into @Employee table
INSERT INTO @Employee (EmployeeID, Name, Department)
VALUES
    (1, 'John Doe', 'HR'),
    (2, 'Jane Smith', 'IT'),
    (3, 'Alice Johnson', 'Finance');

-- Create @EmployeeBackup table
DECLARE @EmployeeBackup TABLE (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50)
);

-- Insert dummy data into @EmployeeBackup table
INSERT INTO @EmployeeBackup (EmployeeID, Name, Department)
VALUES
    (1, 'John Doe', 'HR'),
    (4, 'Bob Brown', 'Marketing');

-- Insert common data into both tables
INSERT INTO @Employee (EmployeeID, Name, Department)
VALUES
    (5, 'Tom White', 'Sales');

INSERT INTO @EmployeeBackup (EmployeeID, Name, Department)
VALUES
    (5, 'Tom White', 'Sales');


SELECT * FROM @Employee
SELECT * FROM @EmployeeBackup

SELECT * FROM @Employee
UNION
SELECT * FROM @EmployeeBackup

SELECT * FROM @Employee
UNION ALL
SELECT * FROM @EmployeeBackup

SELECT * FROM @Employee
INTERSECT
SELECT * FROM @EmployeeBackup

SELECT * FROM @Employee
EXCEPT
SELECT * FROM @EmployeeBackup

Topics covered

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts