6 ms·
Null values and inequality are extremely counterintuitive (in postgres at least). If you run the query: SELECT * FROM my_table WHERE my_column != 5 You woul
by wefarrell 7y ago
Null values and inequality are extremely counterintuitive (in postgres at least). If you run the query:
SELECT * FROM my_table WHERE my_column != 5
You would expect it to return rows that have a null value for my_column, since null is not 5. However that is not the case.
- deleted 7y ago[deleted]
- gfody 7y agothe idea is you don’t know if the null != 5 because null isn’t a value it just marks the absence of a value
- grzm 7y agoNULL in SQL is often interpreted in many different ways. The most helpful I’ve found is to think of it as unknown. Postgres has the IS DISTINCT FROM operator to capture what you’ve intended above: ... WHERE my_column IS DISTINCT FROM 5
- wefarrell 7y agoI wasn't aware, thanks for the tip. Is there any equivalent for sets of values? For example: SELECT * FROM my_table WHERE my_column NOT IN (5, 6)
- unnouinceput 7y ago...and is not null
- wefarrell 7y agoThat will have no effect on the query.
- unnouinceput 7y agofine, here is the full query since extrapolating from incomplete data is hard for you: SELECT * FROM my_table WHERE my_column NOT IN (5, 6) AND my_column IS NOT NULL happy now?
- wefarrell 7y agoThese two queries will always return the same results: SELECT * FROM my_table WHERE my_column NOT IN (5, 6) SELECT * FROM my_table WHERE my_column NOT IN (5, 6) AND my_column IS NOT NULL Because "my_column NOT IN (5, 6)" will exclude NULL values.
- unnouinceput 7y agoRight, my bad. Upper parent was about using IN and having return everything "but" 5 and 6. So here the select that will do that: select mumu from kaka where mumu not in (5, 6) or mumu is null. That will return everything, null included, except for 5 and 6. But wait, there is more. If, for example, performance is the main issue here above query is quite slow. Even on an indexed table on column mumu, it will still do a full scan of the table before returning. How to improve performance in this case? Well, you use LEFT join on itself. Implementation is left as exercise for reader :D.
- Groxx 7y agodoes postgres really not index nulls in a useful way? mysql does, though it may only work efficiently on a single val-or-null comparison at a time.
- unnouinceput 7y agonobody does. MySQL, Oracle, MSSQL, you name it. All sux. That's why I prefer to always declare NOT NULL and have a DEFAULT value when I create tables. Treat the default value as NULL and you'll increase performance a lot.
- AdrianoKF 7y agoSomething along the lines of COALESCE(my_column, -1) might work for you in this case. See [1] for documentation on it in Postgres (it is in ANSI SQL though). [1]: https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL https://www.postgresql.org/docs/current/functions-conditiona...
- gshulegaard 7y agoI think what you are looking for is a compound condition: SELECT * FROM my_table WHERE my_column != 5 OR my_column IS NULL; This is because what you are selecting for is two conditions: when the value is != 5 and when the value is NULL so the result of != 5 is unknown. FWIW I agree with you that NULL's are counter-intuitive. While I am more or less aware of all the various ways to account for them, I still gravitate towards SQL schemas without NULL's since I prefer the intuitiveness of 2VL when writing or reading SQL.
- deleted 7y ago[deleted]
- RmDen 7y agoSame in SQL Server.. this is documented behavior Also a null is no equal to anything.. not even another null This will print false in SQL Server if null = null print 'true' else print 'false'
- magicalhippo 7y ago> Also a null is no equal to anything. Wrong. It is equal to UNKNOWN: https://docs.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql?view=sql-server-ver15#remarks https://docs.microsoft.com/en-us/sql/t-sql/queries/is-null-t...
- RmDen 7y agoso?.. still not equal to anything, two unknowns are not equal if null = null print 'true' else print 'false'
- magicalhippo 7y agoIt's equal to something: the value UNKNOWN. This influences for example how the comparison result is used in compound expressions: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/language-elements...
- arh68 7y agoI don't think that's what it says. If I'm reading it right, (null = null) is unknown, which is falsy (except with ansi_nulls off, then it'll be true). (null is null) is true. I don't think you can test null = (null = null), i.e. null = unknown. Let me know if that's possible somehow, I can't get it working.
- magicalhippo 7y agoSorry brainfart, was responding to the second part, ie comparison to another null.
- 7y ago
- magicalhippo 7y agoI think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value. A NULL value technically means it's unknown. An unknown value might be 5, hence why it's not in the result set. Some abuse NULL to mean "value doesn't exist". But a value that doesn't exist can't be 3, or 42, or any other value that's different from 5, so in that regard shouldn't be part of the result set either. Others again abuse NULL to mean "doesn't apply". And in that case I think it makes sense to include the row in the result set. For example, if I write a query to get all people who's middle name is not "William", I'd most likely want people without middle names included. Maybe we should have introduced NEX (non-existing) and NAP (non-applicable) as possible values in addition to NULL?
- at_a_remove 7y agoAgreed. I have, off and on, labored on a still-incomplete and largely incoherent essay on this topic. NULL is overloaded to the point of some confusion.
- tabtab 7y agoRe: I think the issue here is that SQL should have more "NULL variants" to express why there is no concrete value. No, that would muddy things in my opinion, like it did to JavaScript. Instead, have more operations/functions for dealing with them in a more "normal" way, so that we can say "WHERE x <> 5" and get results one expects. I'm not sure the syntax, and my drafts would take a lot of time to explain. To give a taste, maybe have something like "WHERE ~x <> 5" in which the tilde converts x's value to the type's default, such as a blank in the case of strings. If the different reasons for "emptiness" matter, then usually it suggests the need for a "status" column of some kind so that queries can be done on the reasons. I'd need to study domain specifics to recommend something specific.
- magicalhippo 7y agoBut that would mean you need to be aware that the column can have this property, no? Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction. In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator. In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.
- goatlover 7y agoI wouldn't expect null row values since you're doing a numeric comparison for my_column, and null isn't a number.
- goto11 7y agoNULL in SQL means "unknown value". This is different from most programming languages where null is a special value which typically indicate "nothing". If a value is unknown, you don't know if it is different from 5, so it would be incorrect to return in the query.