# Three accepted solutions

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

• My solution:

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 got "Time Limit Exceeded" when using this sql

• hmm...my query was accepted

• that's right. Overhead overwhelms on small input.

• 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

select c.Name as 'Customers' from Customers c
where c.Id not in (select CustomerId from Orders)

• Can anyone explain to me how the first method works (see below)

SELECT A.Name from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId)

I don't understand "where not exists" part. What does 1 represent?

• @sqlworld1

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.

http://www.w3resource.com/sql/special-operators/sql_exists.php

• @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.