Interview Answer

The perfect project plan is possible if one first documents a list of all the unknowns. Bill Langley

Difference between UNION, MINUS and INTERSECT

January 1, 1970 12:00 am

Comments



Difference between UNION, MINUS and INTERSECT


The employee table contains the following fields

													
													Name           Null     Type         
													-------------- -------- ------------ 
													EMPLOYEE_ID    NOT NULL NUMBER(6)    
													FIRST_NAME              VARCHAR2(20) 
													LAST_NAME      NOT NULL VARCHAR2(25) 
													EMAIL          NOT NULL VARCHAR2(25) 
													PHONE_NUMBER            VARCHAR2(20) 
													HIRE_DATE      NOT NULL DATE         
													JOB_ID         NOT NULL VARCHAR2(10) 
													SALARY                  NUMBER(8,2)  
													COMMISSION_PCT          NUMBER(2,2)  
													MANAGER_ID              NUMBER(6)    
													DEPARTMENT_ID           NUMBER(4)   
													

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.


Solution

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

Comments



Please login to add comments.