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