13th Day, Wohooo! Let’s go. I had three different solution for my Leetcode challenge today. Amazing, isn’t it? From having to scavenge the discussion forums to actually coming up with 3 different solution marks a great achivement for me.

Challenge: Give two tables, Customers and Orders, I had to find the customers who never ordered.

Customers

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders

Id	CustomerId
1	3
2	1

Expected Output

Customers
Henry
Max

Solution 1

SELECT Name as Customers FROM Customers c 
WHERE NOT EXISTS 
      (SELECT CustomerId FROM Orders o 
      WHERE c.Id = o.CustomerId);
Runtime: 705ms 

I used two functions ‘NOT’ and ‘EXISTS’ to return list of Ids that are present in Customers table but not in Orders table. This solution was also the fastest among all the other solutions.

Solution 2

SELECT Name as Customers FROM Customers 
WHERE ID NOT IN (SELECT CustomerId FROM Orders); 
Runtime: 943ms 

Instead of the ‘EXISTS’ function, I used the ‘IN’ function to identfy Ids that are present in Customers table and not in Order table to get the customers who did not place any orders. Since the runtime for this query is longer, ‘EXISTS’ function is faster than ‘IN’ function. I am guessing because we have a ‘WHERE’ clause in the solution 1 to identify the records that match which makes the query run faster.

Solution 3

SELECT Name as Customers
FROM Customers
WHERE Id IN
   (SELECT Id FROM Customers
    MINUS
    SELECT CustomerId FROM Orders) 
Runtime: 1127ms

This query had the longest runtime which shows that this is not the most optimised way to query.

Other solution from discussion forums

SELECT customers.name AS customers 
FROM customers 
LEFT JOIN orders 
ON Customers.Id=Orders.CustomerId
WHERE orders.CustomerId IS NULL;
Runtime: 884ms 

I had actually tried the LEFT JOIN without specifiying the ‘WHERE’ clause which returned me every rows in the Customers table. I need further practice as well as understanding of using JOIN.

• Facebook
• Instagram
• LinkedIn
• GitHub