Skip to content

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.

 

sql-union.jpg

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 UNIONSELECT * FROM Second;

The resultset 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.

sql-union-all.jpg

 

 

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 ALLSELECT * FROM Second;

The resultset 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.

sql-intersect.jpg

NOTE: MySQL does not support INTERSECT operator.

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 INTERSECTSELECT * FROM Second;

The resultset table will look like

ID NAME
2 adam

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.jpg

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 MINUSSELECT * FROM Second;

The resultset table will look like,

ID NAME
1 abhi

 

1 Comment »

Leave a Reply

error: Content is protected !!
%d bloggers like this: