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