Specifying columns in MINUS query yields different results from using *

Question

When I am executing

SELECT * FROM TABLE_NAME WHERE ROWNUM <= 20 
MINUS 
SELECT * FROM TABLE_NAME WHERE ROWNUM <= 10 

I get expected result of SKIP 10 TAKE 10 (ten rows)

but if I specify columns explicitly

SELECT COL1, COL2, COL3 FROM TABLE_NAME WHERE ROWNUM <= 20 
MINUS 
SELECT COL1, COL2, COL3 FROM TABLE_NAME WHERE ROWNUM <= 10

I get single record back. (one row)

Why does this happen? How can I fix this?

Answer 1

Set operations UNION, INTERSECT, MINUS always remove duplicates. (UNION has the cousin UNION ALL which does not).

There is no way to avoid the removal of duplicates when you use MINUS. If all you care about are the first three columns (and want to keep duplicates), you need to apply the MINUS operation first, and only then to select the three columns.

Like so:

SELECT COL1, COL2, COL3 FROM (
    SELECT * FROM TABLE_NAME WHERE ROWNUM <= 20 
    MINUS 
    SELECT * FROM TABLE_NAME WHERE ROWNUM <= 10
);

On further reflection (prompted by Gordon's observation) - while I think this will work (ROWNUM will be the same for the two operands of MINUS), it is not at all clear why MINUS is needed in the first place, either in the original query with select * or in my solution.

The better solution in all cases is to select just once and to use the proper WHERE clause:

WHERE ROWNUM BETWEEN 11 AND 20

I feel dumb for not having thought about it right away... :-)