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

Laurent

Can you give us more information about the query you're trying to write? Maybe there is another way to construct it? – Burdensome

@Burdensome there are sure other ways to write it, but I'm trying to use EXCEPT/MINUS. – Laurent

EXCEPT finally works in Oracle 21c: modern-sql.com/caniuse/except-distinct – Retinite

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.

Brumfield

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. – Laurent

Too funny! I don't use sqlplus that much myself so this would have stumped me as well. – Brumfield

Guys, what about duplicates? e.g: 'a', 'b', 'b' MINUS 'a', 'b' Will I have 'b' as result? – Vannavannatta

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

Gove