Database Management System

Oracle SQL Interview Questions

Pinterest LinkedIn Tumblr

Difference between UNION, MINUS and INTERSECT

UNION

The union uses to combine the result of two or more tables from the select statements. The union select statement should select the same number of fields in the result set. The UNION operator returns only distinct rows that appear in either result. The UNION ALL operator returns all rows from two result sets. The UNION ALL operator does not eliminate duplicate.

MINUS

MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.

INTERSECT

The intersect return the results from two or more select statements.

-- UNION
SELECT * FROM HR.employees WHERE DEPARTMENT_ID = 90
UNION
SELECT * FROM HR.employees WHERE DEPARTMENT_ID = 60;
                                             
 
-- MINUS
SELECT * FROM HR.employees
MINUS
SELECT * FROM HR.employees WHERE DEPARTMENT_ID >= 60;
 
-- INTERSECT
SELECT * FROM HR.employees WHERE EMPLOYEE_ID IN (100, 103, 105)
INTERSECT
SELECT * FROM HR.employees WHERE  EMPLOYEE_ID in (100, 102, 104, 105)
1 2 3 4 5 6 7 8 9 10

Write A Comment