Set operators combine the results of two component queries into a single result.
The different SET operators available in Oracle are,
- UNION ALL
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.
Join combines columns
SET operators combine rows
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
INTERSECToperator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the
INTERSECToperator with other set operators.
Check out Oracle 19c Documentation for more details.