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;
a|p|1|a|p|4
a|p|1|b| |5
a|p|1|c|r|6
b|q|2|a|p|4
b|q|2|b| |5
b|q|2|c|r|6
c|r|3|a|p|4
c|r|3|b| |5
c|r|3|c|r|6
select var1,var2,var3,var4,var5,var6 from a left join b on ((a.var1=b.var4) and (a.var2=b.var5));
a|p|1|a|p|4
b|q|2|||
c|r|3|c|r|6
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 [link] [source]
NULL values are not equal to anything, not even to other NULL values.
Replace b.var6=NULL
by 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 [source]
Many thanks to both of you. There's no substitute for experience :-)