Oracle UNION, UNION ALL, INTERSECT, MINUS

If you have result sets from two queries or more, and you want to combine those result sets or you want to remove duplicate rows or get only the common rows or not common rows, it's time to learn about UNION, UNION ALL, INTERSECT and MINUS queries:

UNION Query:
Allows you to combine the result sets of two or more queries but it removes the duplicate rows between the queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION
SELECT *
  FROM emp
 WHERE deptno = 20;

You can notice that employee with empno = 7566 should come in both select statement but comes once since we used UNION.

UNION ALL Query:
Allows you to combine the result set of two or more queries and returns all rows.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION ALL
SELECT *
  FROM emp
 WHERE deptno = 20;

Since we used UNION ALL, rows from both queries are returned and duplicate rows are not removed.

INTERSECT Query:

It allow you to get only selected rows returned by all queries you are using in INTERSECT Query.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
INTERSECT
SELECT *
  FROM emp
 WHERE deptno = 20;

It returns only one row -returned by both queries-.

MINUS Query:

Retuns rows from first query that are not returned by other queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
MINUS
SELECT *
  FROM emp
 WHERE deptno = 20;

Note:
Each SQL statement within the UNION,UNION ALL,INTERSECT and MINUS query must have the same number of fields in the result sets with similar data types.

Saad,