Getting an Error while using EXCEPT clause

Question

Attached above is the question I am trying to solve. As a start, I wanted to try using the EXCEPT clause in SQL, and get all the values that are not present in the Orders table. This is what I am trying: (Ignore the column names and stuff for now, I just want to test the EXCEPT logic)

SELECT Id, Name FROM Customers
EXCEPT
SELECT O.CustomerId as Id, C.Name AS Name FROM Customers C , Orders O
WHERE C.Id = O.CustomerId

However, I get this error on LeetCode which I am not sure about:

Line 3: SyntaxError: near 'SELECT O.CustomerID as Id, C.Name AS Name FROM Customers C , Orders O WHERE C.Id'

The expected output I want out of the query I wrote is:

Id    Name
2     Henry
4     Max

EDIT: This is a problem on LeetCode, and after looking which version it uses I found it is mysql-server 5.7.21

dangling `)` ... And please *always* disclose the RDBMS and version you are working with. — Erwin Brandstetter, Nov 14 '18 at 00:25
Thanks, but the dangling ) doesnt solve it either. I am not sure which RDBMS and version leetcode uses- I haven't tried running the same locally yet. — The Bolt, Nov 14 '18 at 00:29
It's *your* responsibility to find that out - to make the question useful to others. Error messages from unknown source aren't mighty useful. — Erwin Brandstetter, Nov 14 '18 at 00:33
Much better. One more thing: we very much prefer data as *text*, not as image. — Erwin Brandstetter, Nov 14 '18 at 00:37
@ErwinBrandstetter Thanks, I'll keep that in mind for next time! — The Bolt, Nov 14 '18 at 00:39

score 1 · Answer 1 · answered Nov 14 '18 at 00:30

If you insist on using EXCEPT, make that EXCEPT ALL:

SELECT Id FROM Customers
EXCEPT ALL
SELECT CustomerID FROM Orders;

But I'd rather use LEFT JOIN / .. IS NULL:

SELECT C.*
FROM   Customers C
LEFT   JOIN Orders O ON O.CustomerId = C.Id
WHERE  O.CustomerId IS NULL;

See:

Select rows which are not present in other table

D-Shih · Accepted Answer · 2018-11-14T00:39:43.330

0

I saw your dbms is mysql. Mysql didn't support EXCEPT

Another way you can try to use NOT IN will be easier than EXCEPT

Schema (MySQL v5.7)

CREATE TABLE  Customers(
   id int,
   Name varchar(50)
);

INSERT INTO Customers VALUES (1,'Joe');
INSERT INTO Customers VALUES (2,'Henry');
INSERT INTO Customers VALUES (3,'Sam');
INSERT INTO Customers VALUES (4,'Max');

CREATE TABLE  Orders(
   id int,
   CustomerId int
);

INSERT INTO Orders VALUES (1,3);
INSERT INTO Orders VALUES (2,1);

Query #1

SELECT Id, Name 
FROM Customers
WHERE Id NOT IN (
   SELECT O.CustomerID
   FROM Orders O 
);

| Id  | Name  |
| --- | ----- |
| 2   | Henry |
| 4   | Max   |

View on DB Fiddle

If your dbms support EXCEPT,I would use JOIN instead of , to connect two tables because JOIN have a clearer semantic meaning to connect two tables.

SELECT Id, Name 
FROM Customers
EXCEPT
SELECT O.CustomerID as Id, C.Name AS Name
FROM Customers C JOIN Orders O ON C.Id = O.CustomerID

edited Nov 14 '18 at 00:39

answered Nov 14 '18 at 00:21

D-Shih

44,943
6
31
51

How is this join different from what I do? EDIT: This gives the same error, still. – The Bolt Nov 14 '18 at 00:21
The question asked for returning tuples that are not contained in some table, so my first intuition was to use EXCEPT. – The Bolt Nov 14 '18 at 00:23
@TheBolt What's your dbms? – D-Shih Nov 14 '18 at 00:29
@TheBolt My first query work is sqlfiddle https://www.db-fiddle.com/f/hedPv8dPLCUY2AnpeCDHCy/1 , which is postgresql – D-Shih Nov 14 '18 at 00:29
@TheBolt I edit my answer add more detail about `How is this join different from what I do` – D-Shih Nov 14 '18 at 00:31
@D-Shih I am not sure- this is a problem on LeetCode. Maybe it has to do something with LeetCode only then. I did write a solution with NOT IN and that works, but cannot figure out why a simple EXCEPT clause isnt returning what I expect it would. – The Bolt Nov 14 '18 at 00:31
1

@TheBolt Mysql didn't support `EXCEPT` – D-Shih Nov 14 '18 at 00:38

Getting an Error while using EXCEPT clause

2 Answers2