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 a single set, including all duplicates.
  • INTERSECT --- 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.
  • In order to sort the result, an ORDER BY clause should be part of the last statement.
  • The records from the top query must match the positional ordering of the records from the bottom query.
  • The column names or aliases must be found out by the first select statement.

DEMO - Set up Table and initial Data

Create a table 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 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 

Resut set will take the data from 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.

References