SELECT A.Name from Customers A WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId) SELECT A.Name from Customers A LEFT JOIN Orders B on a.Id = B.CustomerId WHERE b.CustomerId is NULL SELECT A.Name from Customers A WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)
SELECT Name FROM Customers WHERE Customers.Id NOT IN (SELECT b.CustomerId FROM Orders b);
Yup even, I have done with not in... but which is the fastest one..
My answer was in 768 ms with "not in"
The fastest would be (#2 in your comment):
SELECT c.`Name` FROM `Customers` c LEFT JOIN `Orders` o ON(o.`CustomerId` = c.`Id`) WHERE o.`Id` IS NULL
I thought the #2 should be the fastest. But when I run it, #2 is the slowest...
I guess may be the records in database for test are not too many enough.
I added dummy join in the subquery. makes it 635 ms and faster than 93%.
SElECT c.Name FROM Customers c WHERE c.Id not in( SELECT o.CustomerId FROM Orders o JOIN Customers c ON o.CustomerId = c.Id )
Minor improvement over the first solution, beats 95%
SELECT A.Name from Customers A WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId limit 1)
For the third solution we do not need to name B for the Table Orders
Can anyone explain to me how the first method works (see below)
I don't understand "where not exists" part. What does 1 represent?
1 means any. Basically, it tests the subquery for the existence of one or more rows.
In our case, it is
WHERE NOT EXISTS, so the final result should satisfy the condition that the subquery (the 2nd SELECT) has 0 rows return.
@kkamkou Can you explain exactly why the join option will be faster? thank! :)
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.