Set operators combine the results of two component queries into a single result.

The different SET operators available in Oracle are,

• UNION
• UNION ALL
• INTERSECT
• MINUS.

JOIN combines columns from one or multiple tables, as specified in the SELECT statement. Whereas SET operators combines rows from one or multiple tables. This is why SET operators are also called as vertical join.

---

Let’s explore some examples.

--Join query example, combines columns from each table

SELECT Ename, Dname
  FROM Emp, Dept
 WHERE Emp.Deptno = Dept.Deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

While SET operators are executed, the result takes the column names from the 1st component query. Watch..

--SET operators combines each component query resultset into one
SELECT Ename FROM Emp
UNION
SELECT Dname FROM Dept;

ENAME
--------------
ACCOUNTING 
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
OPERATIONS
RESEARCH
SALES
SCOTT
SMITH
TURNER
WARD

18 rows selected.

ORACLE does not have any intelligence to interpret if combined data produced from SET operation is meaningful or not. Oracle SET operation just checks whether SET operation rules are met.

Rules to perform SET operation in Oracle

There are two rules in Oracle RDBMS which must be followed for performing SET operations.

• The number of columns in both the component queries must be same.
• The order of data types must match in both the component queries.

If any rule is violated while SET operation, then Oracle throws error.

--Error! 1st component query has more columns

SELECT Ename, Deptno, Job FROM Emp
UNION
SELECT Dname, Deptno FROM Dept;

ERROR at line 1:
ORA-01789: query block has incorrect number of result columns


--Error! 2nd component query has more columns

SELECT Ename, Deptno FROM Emp
UNION
SELECT Dname, Deptno, Loc FROM Dept;

ERROR at line 1:
ORA-01789: query block has incorrect number of result columns


--Error! Component queries does not have same order of data types

SELECT Ename, Deptno, Job FROM Emp
UNION
SELECT Dname, Loc, Deptno FROM Dept;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression 

---

ORDER BY is not allowed in individual query. ORDER BY can appear once at the end of the query.

SELECT Ename, Empno FROM Emp
 WHERE Deptno = 10
 ORDER BY Ename
UNION
SELECT Ename, Empno FROM Emp
 WHERE Deptno = 30
 ORDER BY Ename;

ERROR at line 9:
ORA-00907: missing right parenthesis


SELECT Ename, Empno FROM Emp
 WHERE Deptno = 10
UNION
(
 SELECT Ename, Empno FROM Emp
  WHERE Deptno = 30
  ORDER BY Ename
);

ERROR at line 9:
ORA-00907: missing right parenthesis


--ORDER BY at the end of query
SELECT Ename, Deptno, Job FROM Emp
 WHERE Deptno = 10
UNION
SELECT Dname, Deptno, Loc FROM Dept
 WHERE Deptno = 30
ORDER BY Ename;

ENAME              DEPTNO JOB
-------------- ---------- -------------
ACCOUNTING             10 NEW YORK
ALLEN                  30 SALESMAN
BLAKE                  30 MANAGER
CLARK                  10 MANAGER
JAMES                  30 CLERK
KING                   10 PRESIDENT
MARTIN                 30 SALESMAN
MILLER                 10 CLERK
OPERATIONS             40 BOSTON
SALES                  30 CHICAGO
TURNER                 30 SALESMAN
WARD                   30 SALESMAN

12 rows selected.

---

All SET operators have equal precedence. Oracle evaluates any expression from left to right. Similarly if a SQL statement contains multiple set operators, then Oracle Database evaluates them from top to bottom unless parentheses explicitly specify another order.

Words from Oracle 19c:

To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.

Check out Oracle 19c Documentation for more details.