SET Operations in SQL

SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions.

In this tutorial, we will cover 4 different types of SET operations, along with example:

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. MINUS

UNION Operation

UNION is used to combine the results of two or more SELECT statements. However it will eliminate duplicate rows from its resultset. In case of union, number of columns and datatype must be same in both the tables, on which UNION operation is being applied.

union set operation in sql


Example of UNION

The First table,

IDName
1abhi
2adam

The Second table,

IDName
2adam
3Chester

Union SQL query will be,

SELECT * FROM First 
UNION
SELECT * FROM Second;

The resultset table will look like,

IDNAME
1abhi
2adam
3Chester

UNION ALL

This operation is similar to Union. But it also shows the duplicate rows.

union all set operation in sql

Example of Union All

The First table,

IDNAME
1abhi
2adam

The Second table,

IDNAME
2adam
3Chester

Union All query will be like,

SELECT * FROM First 
UNION ALL
SELECT * FROM Second;

The resultset table will look like,

IDNAME
1abhi
2adam
2adam
3Chester


INTERSECT

Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same.

NOTE: MySQL does not support INTERSECT operator.

intersect set operatoin in sql

Example of Intersect

The First table,

IDNAME
1abhi
2adam

The Second table,

IDNAME
2adam
3Chester

Intersect query will be,

SELECT * FROM First 
INTERSECT
SELECT * FROM Second;

The resultset table will look like

IDNAME
2adam

MINUS

The Minus operation combines results of two SELECT statements and return only those in the final result, which belongs to the first set of the result.

minus set operation in sql

Example of Minus

The First table,

IDNAME
1abhi
2adam

The Second table,

IDNAME
2adam
3Chester

Minus query will be,

SELECT * FROM First 
MINUS
SELECT * FROM Second;

The resultset table will look like,

IDNAME
1abhi