What Is Set Operation in SQL? | SQL Tutorial

DBMS / By

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

Union

UNION is used to combine the results of two or more Select statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and datatype must be same in both the tables.

union in sql

Example of UNION

The First table,

ID Name
1 abhi
2 adam

The Second table,

ID Name
2 adam
3 Chester

Union SQL query will be,

select * from First
UNION
select * from second

The result table will look like,

ID NAME
1 abhi
2 adam
3 Chester

Union All

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

union all in sql

Example of Union All

The First table,

ID NAME
1 abhi
2 adam

The Second table,

ID NAME
2 adam
3 Chester

Union All query will be like,

select * from First
UNION ALL
select * from second

The result table will look like,

ID NAME
1 abhi
2 adam
2 adam
3 Chester

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. MySQL does not support INTERSECT operator.

intersect in sql

Example of Intersect

The First table,

ID NAME
1 abhi
2 adam

The Second table,

ID NAME
2 adam
3 Chester

Intersect query will be,

select * from First
INTERSECT
select * from second

The result table will look like

ID NAME
2 adam

Minus

Minus operation combines result of two Select statements and return only those result which belongs to first set of result. MySQL does not support INTERSECT operator.

minus in sql

Example of Minus

The First table,

ID NAME
1 abhi
2 adam

The Second table,

ID NAME
2 adam
3 Chester

Minus query will be,

select * from First
MINUS
select * from second

The result table will look like,

ID NAME
1 abhi