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?

Heap-organized tables have no inherent order. Your queries do not have an `order by` so `where rownum <= 20` returns an arbitrary 20 rows and `rownum <= 10` returns an arbitrary 10 rows. You could easily get different results running the same query over again. You likely get different results from the first query doing a table scan and the second query using an index but that's just a guess. If you want a stable set of results, you'd need to specify a deterministic order. Depending on the Oracle version, there may be simpler constructs as well. — Justin Cave, Jun 30 '16 at 14:58
Because you're returning different data, and you are removing duplicate records of the two sets with just the three columns. I can't see your data, but I would assume you have more than three columns in your table that are causing the uniqueness check to fail. — Siyual, Jun 30 '16 at 14:58
And what is surprising that results on three columns would be different from results on all columns? — Gordon Linoff, Jun 30 '16 at 14:58
@Siyual Hi Siyual, I think so too, how do disable unique check? — Matas Vaitkevicius, Jun 30 '16 at 14:59
@MatasVaitkevicius By not doing a `MINUS`? What are you trying to do? — Siyual, Jun 30 '16 at 15:00
Skip 10 take 10 and count a number of rows ? Why don't just `SELECT count(*) - 10 FROM table` ? `MINUS` seems to be a very sophisticated way to do such a count. — krokodilko, Jun 30 '16 at 15:11

score 1 · Answer 1 · answered Jun 30 '16 at 15:01

1

You are using where rownum < XX without an order by (which would be in a subquery).

Hence, the returned rows can be any 10 or 20 rows from the table. In fact, when you run the query multiple times, you might get different results. This is particularly obvious when you run the query on a parallel system -- whichever thread happens to get data might return it first (although this is not quite so obvious for a simple table scan).

You need something like this:

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

answered Jun 30 '16 at 15:01

Gordon Linoff

1,242,037
58
646
786

Same one record with ordering, I think it removes duplicate rows like @Siyual says. – Matas Vaitkevicius Jun 30 '16 at 15:03
1

I understand what you're trying to do here by ordering the tuples... but this isn't necessarily the issue here. It's the minus command and how he's using it. The minus will only return sets that exist in first select and not the second. But using only 3 columns doesn't bring about enough uniqueness on the records. – dvsoukup Jun 30 '16 at 15:06
@dvsoukup . . . You are assuming that the 10 records are a subset of the 20. That is an incorrect assumption. In addition, the OP doesn't specify how many columns the table has; it might have only three columns. – Gordon Linoff Jun 30 '16 at 16:02

score 1 · Accepted Answer · 2016-06-30T16:31:10.040

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... :-)

This answer is incorrect because it assumes that the second subquery will return a subset of rows from the first subquery. That is an incorrect assumption, except in some edge cases (such as the table having 20 or fewer rows). — Gordon Linoff, Jun 30 '16 at 16:03
@GordonLinoff I don't think that's right. I understand your point about ROWNUM not being stable. Not questioning the OP's reasons here; but: if he doesn't care about which rows are returned, my query is in fact correct, because ROWNUM's are set in stone once the processing of the outer query begins. That is, the ROWNUM's in the two operands of the MINUS operation are in fact the same (even though they may change on a different execution of the outer query). — , Jun 30 '16 at 16:11

Specifying columns in MINUS query yields different results from using *

2 Answers2