Set Operators | Union | Union All | Intersect | Minus | Oracle SQL

Set Operators

Set operators are used to combine the results of two or more SELECT queries. These operators allow you to perform set operations on the result sets, such as union, intersection, and difference. The main set operators in Oracle SQL are:

UNION:

 

• Combines the results of two SELECT statements, removing duplicate rows.

• Example:

   

SELECT job FROM emp  

UNION 

SELECT job FROM emp;

Output:

JOB

---------

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

 

UNION ALL:

 

• Similar to UNION, but does not remove duplicate rows. It simply combines all rows from both result sets.

   

• Example:

   

• SELECT job FROM emp
  UNION ALL
  SELECT job FROM emp;
•  
• Output:
• • JOB
  • ---------
  • ANALYST
  • CLERK
  • MANAGER
  • MANAGER
  • MANAGER
  • PRESIDENT
  • SALESMAN
  • SALESMAN
  • SALESMAN
  • ANALYST
  • CLERK
  • CLERK
  • MANAGER
  • PRESIDENT
  • SALESMAN
  
  

INTERSECT:

 

• Returns only the rows that are common to both result sets.

• Example:

• SELECT job FROM emp
  

  INTERSECT
  SELECT job FROM emp;

• 
  

• Output:

• JOB --------- ANALYST CLERK MANAGER PRESIDENT SALESMAN

MINUS (or EXCEPT):

 

• Returns only the unique rows from the first result set that are not present in the second result set.

   

• Example:

• • SELECT job FROM emp
    

    MINUS
    SELECT job FROM emp;

  • 
    

  Output;
• JOB

  ---------