Set Operators

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.
• There different types of SET operations:
  

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

1. 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,

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 resultset table will look like,

ID NAME
1 abhi
2 adam
3 Chester

2. UNION ALL Operations

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

• 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 resultset table will look like,

ID NAME
1 abhi
2 adam
2 adam
3 Chester

3. INTERSECT Operations

• Intersect operation is used to combine two SELECT statements.
• 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.

• 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 resultset table will look like

ID NAME
2 adam

4. MINUS Operations

• The Minus operation combines results of two SELECT statements.
• It will return only those in the final result, which belongs to the first set of the result.
• 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 resultset table will look like,

ID NAME
1 abhi