SQLite Forum

Beginner's problem with left join.
Login

Beginner's problem with left join.

(1) By giomach on 2021-05-01 17:30:05 [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 [link] [source]

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