Question:

What is the difference among UNION, MINUS and INTERSECT?

Answer:

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

Keywords:

© 2017 QuizBucket.org