«Back to Home

Oracle Jump Start
Topics

How To Use Set Operators In Oracle

Description
 
Set operators are used to combine information of similar type from one or more than one table. Set operator data type of corresponding columns must be the same.
 
There are three types of set operator in Oracle are-
 
   • UNION
   • INTERSECT
   • MINUS
 
Set Operators combine two or more queries into one result. Suppose, we want the following details from employee table.
 
   • List of all the different designations in Dept_id 101 and 107.
   • List the jobs common to dept_id 101 and 107.
   • List the jobs unique to dept_id 101.
 
Union
 
It helps in retrieval of rows of first query plus rows of second query, less duplicate rows.
 
Syntax
  1. Select <statement 1>  
  2. UNION  
  3. Select <statement 2>  
  4. [Order-by-clause]  
Example 
  1. Select Emp_job from Employee  
  2. Where Dept_no = 101  
  3. UNION  
  4. Select Emp_job from Employee  
  5. Where Dept_no = 104;  
1
 
Points to be kept in mind while using UNION operator
  •  Two select statements may not contain an ORDER BY CLAUSE, however, the final result of the entire UNION operation can be ordered.
  • The number of columns retrieved by the first, select must be equal to the number of columns retrieved by second select.
  • The data types of columns retrieved by the select statements should be same.
 
Example
  1. Select Emp_id, Emp_name  
  2. From employee  
  3. Where Dept_no = 101  
  4. UNION  
  5. Select Emp_Id, Ename  
  6. from employee  
  7. Where dept_no = 104;  
2 
 
Intersect Operator
 
Description
 
The intersect operator returns the rows, which are common between two sets of rows.
 
Syntax
  1. Select <statement 1>  
  2. INTERSECT  
  3. Select <Statement 2>  
  4. [order-by-clause]  
Example
  1. Select Emp_job  
  2. From employee  
  3. Where Dept_no = 107  
  4. INTERSECT  
  5. Select Emp_job  
  6. from employee  
  7. Where Dept_no = 106;  
3
 
Minus Operator
 
Description
 
Minus operator returns the rows unique to first query.
 
Syntax
  1. Select <statement 1>  
  2. MMINUS  
  3. Select <statement 2>  
  4. [Order-by-clause]   
Example
  1. Select Emp_job from employee  
  2. where dept_no = 101  
  3. Minus  
  4. Select Emp_job from employee  
  5. where dept_no=102;   
4
 
Summary
 
Thus, we learned, Set operators are used to combine the information of similar type from one or more than one table and we learn, how to use these operators with examples.