CODEWITHSUNDEEP
mysqlsql
The Bolt
The Bolt

Reputation: 121

Getting an Error while using EXCEPT clause

enter image description here

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

Upvotes: 0

Views: 172

Answers (2)

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

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:

Upvotes: 1

Related Questions