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

Answer 1

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.

Answer 2

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