Oracle Set Operators

Set operators are used to retrieve data from single or multiple tables.These are also called as virtual joins. Set operators available in oracle are:

UNION
UNION ALL
INTERSECT
MINUS

UNION:

  • Unique elements of sets.
  • The unique clause merges the output of two or more queries into a single set of rows and columns.

Example:

SELECT job from emp
WHERE deptno=20
UNION
SELECT job FROM emp
WHERE deptno=30;

UNION ALL: This returns unique and duplicate values.

Example:

SELECT job from emp
WHERE deptno=20
UNION ALL
SELECT job FROM emp
WHERE deptno=30;

INTERSECT:

  • The intersect operator returns the rows that are common between two sets or rows.
  • The syntax of intersect is same as union operator. Only UNION keyword is replaced with INTERSECT.

Example:

SELECT job from emp
WHERE deptno=20
INTERSECT
SELECT job FROM emp
WHERE deptno=30;

INTERSECT: It returns values from first query those values are not in second query.
Example:

SELECT job from emp
WHERE deptno=20
MINUS
SELECT job FROM emp
WHERE deptno=30;

Powered by k2schools