Reader's Comments

In less trivial uses of UNION, you can use UNION ALL, instructing Oracle not to remove duplicates and saving the sort if you know there aren't going to be any duplicate rows(or maybe don't care)

-- Neal Sidhwaney, December 10, 2002

Another example of using MINUS is shown in the following crazy-looking (and Oracle-specific [1]) query which selects the 91st through 100th rows of a subquery.

with subq as (select * from my_table order by my_id)

select * from subq 
where rowid in (select rowid from subq 
                where rownum <= 100 
                    MINUS
                select rowid from subq
                where rownum <= 90)

[1] The Oracle dependencies in this query are rowid and rownum. Other databases have other means of limiting query results by row position.

-- Kevin Murphy, February 10, 2003

And in PostgreSQL (and MySQL too for that matter) it is as simple as:

select * from my_table order by my_id limit 90,10

An easier way for Oracle (according to a random post in a devshed.com forum I googled) would be like this:

select * from my_table order by my_id where rownum between 90,100

(Though the whole point about how to use MINUS is well taken)

-- Gabriel Ricard, February 26, 2003

Oops. I was wrong. Phil emailed me and explained that my rownum example won't work (just goes to show that not everything you find on the internet is right!).

-- Gabriel Ricard, March 17, 2003