[Qgis-user] Problem filtering table on columns with NULL values

[Bernd Vogelgesang]

Hi Chris,

I know how to query for NULL or NOT NULL.

I was just wondering that I have to specify that at all when querying
for !='xyz'.

That is a behaviour I did not expect nor ever encountered before. Of
course, in my own data, there wouldn't be a NULL at all, but this stuff
was from some database-agnostic who has even less understanding of tidy
data handling than me ;)

so, the OR IS NULL did the trick.

Thanx a lot to all,

Bernd

On 15.10.20 19:00, chris hermansen wrote:
> Bernd, "qgis-user", list
>
> On Thu, Oct 15, 2020 at 9:47 AM <qgis-user at stripfamily.net> wrote:
>
>> While waiting for a more authoritative answer from Chris, I'd suggest
>> trying
>>      !='xyz' OR is NULL
>>
> "authoritative" wow - I've been called a lot of things in my life but
> that's a first!  Thanks!
>
> I like your solution.  Bernd more comments below:
>
>> On 10/15/2020 10:12 AM, Bernd Vogelgesang wrote:
>>
>> Hi Chris,
>>
>> I'm a bit confused: I just stated, that I use a filter !='xyz', but not
>> only these rows are ommited, but also those with NULL values.
>> I do not try to filter for NULL, I just want the query to NOT filter
>> them out.
>>
>> So what I can do to keep the NULLs and just get rid of the xyz?
>>
>>
> Let's look at a record where fieldname is NULL.  Then neither
>
> fieldname = 'xyz'
>
> nor
>
> fieldname != 'xyz'
>
> are true.  That's because NULL is neither equal to nor not equal to 'xyz'.
>
> I find the easiest way to wrap my head around this is to think of NULL as
> meaning "I don't know what the value is".  If you use that definition, you
> can't say whether a field whose value is unknown is equal to some specific
> value or not equal to it.  Nor can you say that two fields, both with
> unknown values, are equal to one another.
>
> As far as I know, most programming languages like Python, Java, C, etc
> expect us to compare a value with null, nil, None etc using regular boolean
> operators.  SQL isn't like that; it provides specifically IS and IS NOT
> operators for checking for NULL.
>
> Here are a couple of references that might help:
>
> https://en.wikipedia.org/wiki/Null_(SQL)
> https://www.w3schools.com/sql/sql_null_values.asp
>
>
>
>