Set Operators (SQL) - UNION, UNION ALL, INTERSECT, EXCEPT

Introduction

Set operations allow the results of multiple queries to be combined into a single result set. Set operators include,

Four Set Operators

UNION. Combine two or more result sets into a single set without duplicates.
UNION ALL. Combine two or more result sets into one set, including all duplicates.
INTERSECT. It takes the data from both result sets, which are in common.
EXCEPT. Takes the data from the first result set, but not the second (i.e., no matching to each other)

Rules on Set Operations

The result sets of all queries must have the same number of columns.
In every result set, the data type of each column must match the data type of its corresponding column in the first result set.
An ORDER BY clause should be part of the last statement to sort the result.
The records from the top query must match the positional ordering of the records from the bottom query.
The first select statement must find out the column names or aliases.

Demo. Set up Table and initial Data

Create a table for Students with two columns: id, name

CREATE TABLE [dbo].[Students](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Name] [nvarchar](max) NOT NULL  
  
 CONSTRAINT [PK_dbo.Students] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO

Create a table of Teachers with two columns: id, name

CREATE TABLE [dbo].[Teachers](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Name] [nvarchar](max) NOT NULL,  
 CONSTRAINT [PK_dbo.Teachers] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO

Insert initial Data

insert dbo.Students    
Values ('Greg'),    
       ('George'),    
       ('Helen'),    
       ('Tom')   
  
insert into dbo.Teachers    
Values ('Greg'),    
       ('George'),    
       ('David')

The result will be,

Set Operator. UNION

SELECT * FROM Students  
UNION  
SELECT * FROM Teachers

Resut set will combine two result sets into a single set, without duplicates.

Venn diagram

Set Operator. UNION ALL

SELECT * FROM Students  
UNION ALL  
SELECT * FROM Teachers

Resut set will combine two or more result sets into a single set, including all duplicates.

Venn diagram

Set Operator: INTERSECT

SELECT * FROM Students  
INTERSECT 
SELECT * FROM Teachers

Resut set will take the data from both result sets, which are in common.

Venn diagram

Set Operator: EXCEPT

SELECT * FROM Students  
EXCEPT 
SELECT * FROM Teachers

The Resut set will take the data from the first result set but not the second.

Venn diagram

Note. Oracle Set Operator: MINUS == EXCEPT (SQL Server

Summary

This article efficiently helps me to understand the set operators: UNION, UNION ALL, INTERSECT, and EXCEPT in SQL.