EXCEPT keyword in Oracle

Question

I'm trying to use the EXCEPT keyword in Oracle 10.1.0.2.0, but kept getting error 'Unknown Command'. I've tried googling around and someone said the keyword is MINUS, so I used MINUS, instead, but I still got the same error. Any idea? Thanks.

So here's my query. I'm finding the name of students who enrolls in ALL courses with course number > 500

SELECT s.name
FROM Students s
WHERE NOT EXISTS
  (
    SELECT c.id
    FROM Courses c
    WHERE c.number > 500

    MINUS

    SELECT e.course_id
    FROM Enrollment e
    WHERE e.student_id = s.id
  );

Can you give us more information about the query you're trying to write? Maybe there is another way to construct it? — Aiias, Mar 16 '13 at 02:50
@Aiias there are sure other ways to write it, but I'm trying to use EXCEPT/MINUS. — 0x56794E, Mar 16 '13 at 02:52
EXCEPT finally works in Oracle 21c: https://modern-sql.com/caniuse/except-distinct — Markus Winand, Nov 29 '21 at 18:37

score 33 · Accepted Answer · edited May 23 '17 at 10:31

33

Oracle MINUS is an operator; it's equivalent to EXCEPT in SQL Server. Here is a previous post explaining the difference. Here's a trivial example:

SELECT a, b, c
FROM   table_a
MINUS
SELECT a, b, c
FROM   table_b

If you still have problems, add the complete query you are using to your question; it's likely a simple syntax error.

edited May 23 '17 at 10:31

Community

1
1

answered Mar 16 '13 at 03:10

BellevueBob

9,498
5
29
56

1

OOh! I figured out what was wrong with my query. There was actual "NOTHING" wrong, except for the fact that sqlplus doesn't seem to like the empty lines I put before and after the MINUS keyword. – 0x56794E Mar 16 '13 at 03:52
Too funny! I don't use sqlplus that much myself so this would have stumped me as well. – BellevueBob Mar 16 '13 at 04:10
Guys, what about duplicates? e.g: 'a', 'b', 'b' MINUS 'a', 'b' Will I have 'b' as result? – Rudziankoŭ Jan 27 '16 at 13:11

Lukasz Szozda · Answer 2 · 2020-02-15T11:30:04.400

Oracle 20c will support EXCEPT/EXCEPT ALL keywords.

SELECT col1, col2
FROM t1
EXCEPT
SELECT col1, col2
FROM t2;

or EXCEPT ALL if you want to handle duplicates:

SELECT col1, col2
FROM t1
EXCEPT ALL
SELECT col1, col2
FROM t2;

4.6 Set Operators

Set operators combine the results of two component queries into a single result.

EXCEPT All distinct rows selected by the first query but not the second

EXCEPT ALL All rows selected by the first query but not the second including duplicates

EXCEPT keyword in Oracle

2 Answers2