Set Operators in Oracle with Examples

Set Operators in Oracle with Examples | Set operators in the Oracle database are used to join the results of two or more SELECT statements from single or multiple tables.

The set operators in the Oracle database are also called vertical Joins. Oracle has the following set operators:-

• UNION
• UNION ALL
• INTERSECT
• MINUS

Oracle Set Operators

UNION Set Operator:- UNION set operator returns the combined result in the sorted order (ascending by default), and it excludes the duplicate results. Only one result is displayed for the duplicate results. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the UNION Set for them will be {a,b,d,x}

UNION ALL Set Operator:- UNION ALL set operator is similar to UNION set operator but it includes the duplicate results in the final result.  The UNION ALL set operator gives better performance as compared to the UNION set operator. Because resources are not used in filtering duplicates and sorting the result set. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the UNION ALL Set for them will be {a,b,x,a,a,b,c,c,d}

INTERSECT Set Operator:- INTERSECT set operator in Oracle returns the common values in sorted order (ascending by default). It produces an unduplicated result. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the INTERSECT Set for them will be {a,b}

MINUS Set Operator:- The MINUS set operator in Oracle returns the results that are in the result of the first query but not in the result of the second query. It produces non-duplicate results in ascending order by default. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the INTERSECT Set for them will be {x}

The UNION and INTERSECT set operators are commutative, which means the order of queries is not important, for different orders we will get the same output.

We can combine multiple queries using the set operators, and the set operator can be part of the subqueries. All set operators have equal precedence and when SQL statements contain multiple set operators then evaluation starts from left to right or top to bottom. But the order may differ if we use parentheses explicitly.

Example on Set Operators in Oracle Database

To practice Set operators we will use EMP and DEPT table of Oracle database given under SCOTT user. Display EMP table,

SELECT empno, ename, job, deptno FROM emp;

Output:-

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10
14 rows selected.

Display DEPT table,

SELECT deptno, dname FROM dept;

Output:-

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

From the EMP table, display the jobs where DEPTNO = 10,

SELECT job FROM emp WHERE deptno = 10;

Output:-

JOB
———
MANAGER
PRESIDENT
CLERK

From the EMP table, display the jobs where DEPTNO = 20,

SELECT job FROM emp WHERE deptno = 20;

Output:-

JOB
---------
CLERK
MANAGER
ANALYST
CLERK
ANALYST

For better understanding purposes, write the above two results (jobs for the deptno=10, and 20) on the paper and then observe the result after using Set operators.

1. UNION Set Operator Example

For multiple select queries, the UNION set operator returns the combined result in sorting order and removes all duplicate values. The UNION set operator doesn’t ignore the NULL value.

SELECT job FROM emp WHERE deptno = 10
UNION
SELECT job FROM emp WHERE deptno = 20;

Output:-

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT

2. UNION ALL Set Operator Example

UNION ALL set operator gives combined results of all select queries, and it also includes the duplicate results in the final result. 

SELECT job FROM emp WHERE deptno = 10
UNION ALL
SELECT job FROM emp WHERE deptno = 20;

Output:-

JOB
---------
MANAGER
PRESIDENT
CLERK
CLERK
MANAGER
ANALYST
CLERK
ANALYST
8 rows selected.

3. INTERSECT Set Operator Example

The INTERSECT set operator in Oracle returns the common value and produces an unduplicated result.

SELECT job FROM emp WHERE deptno = 10
INTERSECT
SELECT job FROM emp WHERE deptno = 20;

Output:-

JOB
---------
CLERK
MANAGER

4. MINUS Set Operator Example

The MINUS set operator in Oracle returns the results that are in the result of the first query but not in the result of the second query. It produces an unduplicated result.

SELECT job FROM emp WHERE deptno = 10
MINUS
SELECT job FROM emp WHERE deptno = 20;

Output:-

JOB
---------
PRESIDENT

More on the Return value of Set Operators

Whenever we are using set operators then the corresponding expressions or columns must match in number and must belong to the same data type group (such as numeric or character) else we get errors. Oracle database will not perform implicit conversion if corresponding columns in the component queries belong to the different data type groups.

SELECT job FROM emp WHERE deptno = 10
UNION
SELECT empno FROM emp WHERE deptno = 10;

Output:-

SELECT job FROM emp WHERE deptno = 10
*
ERROR at line 1:
ORA-01790: expression must have same datatype
as corresponding expression

Here, the JOB column belongs to the varchar2 data type but the EMPNO column belongs to the number data type. Therefore, both are not compatible and give errors.

If component queries select character data, then the data type of the return values is determined as follows:-

• If both queries select the values of the CHAR data type of equal length then the return value will have a CHAR data type of the same length. But if they have different lengths of CHAR datatype then the return value is VARCHAR2 with the larger CHAR value.
• If anyone query has select values of VARCHAR2 datatype then the return value has  VARCHAR2 datatype.

When component queries select numeric data, then the data type of the return values is determined by numeric precedence:

• If at least one query has select values of BINARY_DOUBLE type then the datatype of the return value is  BINARY_DOUBLE.
• If no query has select values of BINARY_DOUBLE type, but any query selects values of type BINARY_FLOAT then the returned values have datatype BINARY_FLOAT.

Matching the SELECT statement

In Oracle, we can also retrieve data from multiple queries by using set operators, when corresponding expressions do not belong to the same data type also. In this case, we must use appropriate type conversion functions.

To match the column list explicitly, NULL columns are inserted at the missing positions so as to match the count and data type of selected columns in each SELECT statement. For number columns, zero can also be substituted to match the type of columns selected in the query. Example:-

SELECT deptno FROM emp
UNION
SELECT dname FROM dept;

Output:-

SELECT deptno FROM emp
*
ERROR at line 1:
ORA-01790: expression must have same datatype
as corresponding expression

The deptno is of number data type but dname is of varchar2 data type. Solution:-

SELECT deptno, TO_CHAR(NULL) "deptnames" FROM EMP
UNION
SELECT TO_NUMBER(NULL), dname FROM DEPT;

Output:-

    DEPTNO deptnames
---------- --------------
        10
        20
        30
           ACCOUNTING
           OPERATIONS
           RESEARCH
           SALES
7 rows selected.

It can be also written as,

SELECT deptno, TO_CHAR('---') "deptnames" FROM EMP
UNION
SELECT TO_NUMBER('0'), dname FROM DEPT;

Output:-

    DEPTNO deptnames
---------- --------------
         0 ACCOUNTING
         0 OPERATIONS
         0 RESEARCH
         0 SALES
        10 ---
        20 ---
        30 ---
7 rows selected.

By default set operators always return the first select query column name or alias name as the column heading. Examples:-

SELECT dname FROM DEPT
UNION
SELECT ename FROM EMP;

Output:-

DNAME
--------------
ACCOUNTING
ADAMS
……….
………..

ORDER BY Clause Used with SET Operators

We can also use the ORDER BY clause with set operators, but it should be used only with the last SELECT statement. Example:-

SELECT empno, ename, job FROM emp
WHERE deptno = 10
UNION
SELECT empno, ename, job FROM emp
WHERE deptno = 20
ORDER BY empno;

Output:-

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7566 JONES      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7876 ADAMS      CLERK
      7902 FORD       ANALYST
      7934 MILLER     CLERK
8 rows selected.

Limitations of Set Operators in Oracle Database

The set operators have the following limitations,

• Set operators can’t be applied on columns having type BLOB, CLOB, BFILE, VARRAY, or nested table.
• Only the UNION ALL set operator is valid on LONG columns.
• Set operators can’t be used in SELECT statements containing TABLE collection expressions.
• If the select list preceding the set operator contains an expression, then we must provide a column alias for the expression in order to refer to it in the ORDER BY clause.
• We can’t also specify the FOR UPDATE clause with the set operators.
• We can’t specify the ORDER BY clause in the subquery of these operators.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search