Some databases, such as PostgreSQL and Teradata, natively support “ALL” keyword with SET operators MINUS (EXCEPT) and INTERSECT. As of current version, Oracle does not support these features; however, a little trick can help emulating the missing functionality.

Let’s see an example. We want to see job titles from department #20 that don’t exist in department #30.

SELECT job FROM emp WHERE deptno=20 MINUS SELECT job FROM emp WHERE deptno=30; --Result: JOB --------- ANALYST

If Oracle supported MINUS ALL the result of the following SQL would be quite different:

SELECT job FROM emp WHERE deptno=20 MINUS ALL SELECT job FROM emp WHERE deptno=30; --Result (possible): JOB --------- ANALYST ANALYST CLERK

The following trick with Analytical function ROW_NUMBER helps with Oracle SQL solution:

SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn FROM emp WHERE deptno=20 MINUS SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn FROM emp WHERE deptno=30; JOB RN --------- ---------- ANALYST 1 ANALYST 2 CLERK 2

If we ignore (or hide by selecting from the above result) RN column, we would get exactly what we need:

WITH x AS ( SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn FROM emp WHERE deptno=20 MINUS SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn FROM emp WHERE deptno=30 ) SELECT job FROM x; JOB --------- ANALYST ANALYST CLERK

The same trick works fine with mimicking INTERSECT ALL statement.

The rule is simple:

SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn FROM WHERE ... INTERSECT SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn FROM WHERE ...

Just make sure to list all selected columns in PARTITION BY clause.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.