Home / mySQL / Set Operation in SQL- UNION, UNION ALL, INTERSECT,MINUS operator

Set Operation in SQL- UNION, UNION ALL, INTERSECT,MINUS operator

Hi Guys,
Welcome to sql set operations. SQL support some set of operations to be performed on MYSQL tadabase table. These are used for getting meaningful results from table, based on different conditions.

Union




MySQL UNION operator to combine two or more SELECT statements into a single result set. UNION operator helps to select rows one after the other from several tables into a single table. In here 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

SYNTAX

SELECT column1, column2 from tablename1 UNION
SELECT column1, column2 from tablename2





OR

SELECT * from tablename1 UNION SELECT * from tablename2

The UNION operator selects only distinct values by default

sql-union




Example of UNION

The Employee1 table




Employee_ID Employee_Name
1 Jose
2 Thomas
3 Joseph

The Employee2 table

Employee_ID Employee_Name
1 Jose
2 Thomas
3 Joseph
4 Mathew

Union SQL query will be:

select * from Employee1
UNION
select * from Employee2




The result table will look like,




Employee_ID Employee_Name
3 Joseph
4 Mathew

In UNION eliminate the duplicate values of data.

Union ALL

The UNION operator shows only distinct values.But in UNION ALL, it also shows duplicate values.

SYNTAX

SELECT column_name(s) FROM tablename1
UNION ALL
SELECT column_name(s) FROM tablename2




union_all

Example of UNION ALL

The Employee1 table




Employee_ID Employee_Name
1 Jose
2 Thomas
3 Joseph

The Employee2 table

Employee_ID Employee_Name
3 Joseph
4 Mathew

Union ALL SQL query will be:

select * from Employee1
UNION ALL
select * from Employee2

The result table will look like,




Employee_ID Employee_Name
1 Jose
2 Thomas
3 Joseph
3 Joseph
4 Mathew

it shows duplicate values also

INTERSECT

The INTERSECT display only common values from two tables in SELECT query.

SYNTAX

select * from Tablename1
INTERSECT
select * from Tablename2




sql-intersect

Example of INTERSECT

The Employee1 table




Employee_ID Employee_Name
1 Jose
2 Thomas
3 Joseph

The Employee2 table




Employee_ID Employee_Name
3 Joseph
4 Mathew

INTERSECT SQL query will be:

select * from Employee1
INTERSECT
select * from Employee2

The result table will look like,

Employee_ID Employee_Name
3 Joseph




MYSQL doesn’t support INTESECT operator.

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.

SYNTAX

select * from tablename1
MINUS
select * from Tablename2




minus

Example of MINUS

The Employee1 table

Employee_ID Employee_Name
1 Jose
2 Thomas





The Employee2 table

Employee_ID Employee_Name
2 Thomas
4 Mathew




MINUS SQL query will be:

select * from Employee1
MINUS
select * from Employee2

The result table will look like,

Employee_ID Employee_Name
1 Jose




If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com