CODEWITHSUNDEEP
sqlleft-join
MonoThreaded
MonoThreaded

Reputation: 12033

SQL Left Join Oddity

Considering the following tables

sessions                   users
========                   =====
id uid sid                 id name
----------                 -------
7  1   192.168.0.10        1  AAA
8  2   NULL                2  BBB
12 2   192.168.0.13        3  CCC
15 20  NULL

Why is 

SELECT u.name FROM `users` u 
LEFT JOIN sessions s 
ON u.id=s.uid 
WHERE s.sid IS NULL

returning

BBB
CCC

Why is this retuning CCC when the second uid is invalid. I was expecting NULL.
Please note that I do not want to fix the query, just to understand the result.

Upvotes: 0

Views: 56

Answers (2)

HLGEM
HLGEM

Reputation: 96552

What the query is asking for is any users who do not have a session or who had a session that did not get assigned an s.sid value.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You don't need a LEFT JOIN for this, because you do not care about non-matches:

SELECT u.name
FROM `users` u JOIN
     sessions s 
     ON u.id = s.uid 
WHERE s.sid IS NULL;

Upvotes: 3

Related Questions