A solution using NOT IN and another one using LEFT JOIN


  • 19
    O

    605 ms

    SELECT Name as Customers from Customers
    LEFT JOIN Orders
    ON Customers.Id = Orders.CustomerId
    WHERE Orders.CustomerId IS NULL;
    

    675ms

    SELECT Name as Customers from Customers
    WHERE Id NOT IN (SELECT CustomerId from Orders);

  • 1
    B

    Can anyone explain what does the last line of first solution

    WHERE Orders.CustomerId IS NULL;
    

    mean?


  • 1
    O

    The left JOIN with the condition "Customers.Id = Orders.CustomerId" gives the list of customers that respect the condition including the ones that do not. The ones that do not respect the condition are the customers that never order, and they have an Orders.CustomerId that does not exist, an Order.CustomerId that is NULL.


  • 0

    I have almost the same solution as you, but I use
    "WHERE Orders.Id IS NULL" instead, I think it will make more sense if you use Orders.Id is null, which means after left join, the customers who never order should have a NULL in the joined table.


  • 0
    S
    This post is deleted!

  • 0
    Z

    @charles27
    "WHERE Orders.Id IS NULL" and "WHERE Orders.CustomerId IS NULL" are all right!!
    the join table shows below:

    +-------+---------+------------+
    | Name  | OrderId | CustomerId |
    +-------+---------+------------+
    | Joe   |       2 |          1 |
    | Henry |    NULL |       NULL |
    | Sam   |       1 |          3 |
    | Max   |    NULL |       NULL |
    +-------+---------+------------+
    

  • 0
    This post is deleted!

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.