SQL: Set Operations

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.

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.

Example of UNION

The First table,

The Second table,

Union SQL query will be,

SELECT * FROM First UNIONSELECT * FROM Second;

The resultset table will look like,

UNION ALL

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

Example of Union All

The First table,

The Second table,

Union All query will be like,

SELECT * FROM First UNION ALLSELECT * FROM Second;

The resultset table will look like,

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.

Example of Intersect

The First table,

The Second table,

Intersect query will be,

SELECT * FROM First INTERSECTSELECT * FROM Second;

The resultset table will look like

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.

Example of Minus

The First table,

The Second table,

Minus query will be,

SELECT * FROM First MINUSSELECT * FROM Second;

The resultset table will look like,