How to mimic MINUS ALL and INTERSECT ALL in Oracle

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s