Beginner's problem with left join.

(1) By giomach on 2021-05-01 17:30:05 [link]

A beginner's problem. I have a screenshot, but I will try to retype. Using 3.35.5 through sqlite3.exe.<br>
<i>select var1,var2,var3,var4,var5,var6 from a left join b;</i><br>
a|p|1|a|p|4<br>
a|p|1|b| |5<br>
a|p|1|c|r|6<br>
b|q|2|a|p|4<br>
b|q|2|b| |5<br>
b|q|2|c|r|6<br>
c|r|3|a|p|4<br>
c|r|3|b| |5<br>
c|r|3|c|r|6<br>
<i>select var1,var2,var3,var4,var5,var6 from a left join b on ((a.var1=b.var4) and (a.var2=b.var5));</i><br>
a|p|1|a|p|4<br>
b|q|2|||<br>
c|r|3|c|r|6<br>
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.<br>
<i>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;</i><br>
BUT NO ROWS ARE RETRIEVED.  Same result with "<i>where b.var6=''</i>.<br>
How can I do this?<br>
Thanks for any ideas.

(2) By Harald Hanche-Olsen (hanche) on 2021-05-01 17:45:26 in reply to 1

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]

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]

Many thanks to both of you.  There's no substitute for experience :-)