-2

For this leetcode question: https://leetcode.com/problems/customers-who-never-order/

I know one correct way to solve this is:

SELECT c.name as Customers
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.id = o.customerid
WHERE o.customerid IS NULL;

I want to know why the following does not work:

SELECT c.name as Customers
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.id = o.customerid
WHERE c.id <> o.customerid;

Specifically, WHERE c.id <> o.customerid or even WHERE c.id NOT IN (o.customerid) does not work. The NOT IN works when you use a subquery in the where clause but not with a join the way this is written.

Why is that? I want to intuitively understand what's going on.

Sarah
  • 1
  • 1
  • Your query looks like it's asking for records which satisfy both equal AND not equal conditions. Seems like a tall order. – Tim Lehner Aug 23 '22 at 18:26
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Aug 24 '22 at 06:18

1 Answers1

1

Remember that SQL defines tri-valued logic where predicates can evaluate to: true, false, or unknown.

In the second query the predicate c.id <> o.customerid evaluates to unknown when o.customerid is null.

Then, the WHERE clause selects only rows that evaluate to "true" and discards rows that evaluate to "false" and "unknown". In your case the rows you want will evaluate to unknown and, therefore, will be discarded.

The same logic applies to c.id NOT IN (o.customerid). It also evaluates to unknown (not true, not false) when o.customerid is null.

The Impaler
  • 45,731
  • 9
  • 39
  • 76