Post
Ask Question

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.


Similar Articles