Marcus Leon Marcus Leon - 7 months ago 30
SQL Question

Prevent Oracle minus statement from removing duplicates

Given these two tables:

CREATE TABLE TEST1 (TEST VARCHAR2(1 BYTE))
CREATE TABLE TEST2 (TEST VARCHAR2(1 BYTE))


Where TEST1 has two rows both with the value 'A' and TEST2 has one row with the value 'B'.

When I run this command:

SELECT TEST FROM TEST1
MINUS
SELECT TEST FROM TEST2


I get the output:

Test
-----
A


It appears that MINUS removes the duplicates (as there are two 'A' rows in TEST1).

How can you get MINUS query to include duplicate values (return two 'A' rows)?

Answer

Oracle supports multiple columns in the IN statement, so you can write:

SELECT a, b, c
FROM table1
WHERE (a,b,c) not in (
    select a,b,c from table2
)
Comments