WHAT IS THE DIFFERENCE AMONG UNION, MINUS AND
INTERSECT?
UNION combines the results from 2 tables and eliminates duplicate
records from the result set.
MINUS operator when used between 2 tables, gives us all the rows from
the first table except the rows which are present in the second table.
INTERSECT operator returns us only the matching or common rows between 2
result sets.
To understand these operators, let’s see some examples. We will use two
different queries to extract data from our emp table and then we will perform
UNION, MINUS and INTERSECT operations on these two sets of data.
UNION
SELECT * FROM EMPLOYEE WHERE ID = 5
UNION
SELECT * FROM EMPLOYEE WHERE ID = 6
ID
|
MGR_ID
|
DEPT_ID
|
NAME
|
SAL
|
DOJ
|
5
|
2
|
2.0
|
Anno
|
80.0
|
01-Feb-2012
|
6
|
2
|
2.0
|
Darl
|
80.0
|
11-Feb-2012
|
MINUS
SELECT * FROM EMPLOYEE
MINUS
SELECT * FROM EMPLOYEE WHERE ID >
2
ID
|
MGR_ID
|
DEPT_ID
|
NAME
|
SAL
|
DOJ
|
1
|
2
|
Hash
|
100.0
|
01-Jan-2012
|
|
2
|
1
|
2
|
Robo
|
100.0
|
01-Jan-2012
|
INTERSECT
SELECT * FROM EMPLOYEE WHERE ID IN
(2, 3, 5)
INTERSECT
SELECT * FROM EMPLOYEE WHERE ID IN
(1, 2, 4, 5)
ID
|
MGR_ID
|
DEPT_ID
|
NAME
|
SAL
|
DOJ
|
5
|
2
|
2
|
Anno
|
80.0
|
01-Feb-2012
|
2
|
1
|
2
|
Robo
|
100.0
|
01-Jan-2012
|
No comments:
Post a Comment