Beginner's problem with left join.
(1) By giomach on 2021-05-01 17:30:05 [link] [source]
A beginner's problem. I have a screenshot, but I will try to retype. Using 3.35.5 through sqlite3.exe.
select var1,var2,var3,var4,var5,var6 from a left join b;
select var1,var2,var3,var4,var5,var6 from a left join b on ((a.var1=b.var4) and (a.var2=b.var5));
Now I want to select rows in the result by the value of b.var6. In particular, I want to include rows like the second of the three rows above.
select var1,var2,var3,var4,var5,var6 from a left join b on ((a.var1=b.var4) and (a.var2=b.var5)) where b.var6=NULL;
BUT NO ROWS ARE RETRIEVED. Same result with "where b.var6=''.
How can I do this?
Thanks for any ideas.
(2) By Harald Hanche-Olsen (hanche) on 2021-05-01 17:45:26 in reply to 1 [source]
NULL values are not equal to anything, not even to other NULL values.
b.var6 IS NULL.
(There are also comparison operators
IS DISTINCT FROM and
IS NOT DISTINCT FROM that can tell the difference between null and non-null values. But that is getting too verbose, when you already know that you want to compare with NULL.)
(3) By Balaji Ramanathan (balaji) on 2021-05-01 17:49:38 in reply to 1 [link] [source]
You cannot use "=" when comparing with NULL's. Replace "=" with "is" and you will get the results you are hoping for. Nothing ever equals NULL (in the sense that "=" implies). You can use "is" for even regular comparisons where "=" will work (like comparing non-null numbers), so I have gotten into the habit of simply using "is" wherever I would think of using "=".
(4) By giomach on 2021-05-01 18:18:12 in reply to 1 [link] [source]
Many thanks to both of you. There's no substitute for experience :-)