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

[chris hermansen]

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




-- 
Chris Hermansen · clhermansen "at" gmail "dot" com

C'est ma façon de parler.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20201015/2bb8a0b6/attachment.html>