9 ms·
Null Values in SQL Queries
- mwexler 7y agoNull values are so important in representing data. But they cause so much confusion in a) unexpected behaviors in queries and b) inconsistent handling across various engines... I sometimes wish <whisper> that they hadn't been included in the spec at all </whisper>. But then I come to my senses again, and go fix yet another bugged query for an analyst who didn't account for nulls in the data.
- paulryanrogers 7y agoDoes it make sense to coalesce them away in a view? I thought most analysts are given star schemas implemented by views or ETL'd data anyway.
- mwexler 7y agoDepends on the level of sophistication of the analyst, and if nulls have a meaning or value to the result. Also, at a certain point, knowing that nulls are present gives you yet another measure of dq: not knowing if they are present and hidden vs. visible and countable can be the difference between a wrong answer vs. just an uncertain one.
- salzig 7y agothere is something "missing". The SQL spec specifies `null = null` to be "unknown", where i sometimes expect "true". For MSSQL this can be configured using `SET ANSI_NULLS { ON | OFF }`. AFAIK MySQL can't be configured. Don't know about Postgres.
- hobs 7y agoFor what its worth, don't do this - pretty much all db code and practitioners expect three valued logic, not two.
- quietbritishjim 7y agoFor postgres you can just use the separate operator IS NOT DISTINCT FROM to explicitly request this behaviour. In SQLite I think it's just IS. I assume most SQL databases have something similar, and that's a far better solution than applying a global config.
- himinlomax 7y agoThe standard makes sense if you go back to the theoretical basis of SQL. It seems somewhat counter-intuitive only when you think of NULL as a value you set in a cell. When it's the result of a relational operation (such as a LEFT JOIN) however, the default makes sense while considering NULLs as equal to each other is typically not useful.
- juped 7y ago>For example, Oracle database won’t allow you to have an empty string. Anytime Oracle database sees an empty string, it automatically converts the empty string into a NULL value. Damn. This is how you do enterprise. I might be the only person who likes SQL nulls. If you learn how they work up front, they're useful and not really that confusing. But if I ran into weird behaviors like this, I might hate them too.
- johannes1234321 7y agoRight, I am a fan of SQL NULl as well. It is nicely consistent - anything NULL in - you get NULL out. Clearly telling that you get undefined data. Silently converting to empty string, zero, or therelike would eventually return garbage for harder to debug reasons. That Oracle behavior annoys me each time, though.
- tabtab 7y agoRe: Silently converting to empty string, zero, or therelike would eventually return garbage for harder to debug reasons. It's never been a problem with strings in my many decades of experience, unless somebody does something which I consider poor system engineering. Nearby I invited a solid use-case illustrating a real string need.
- Starwatcher2001 7y agoI like nulls too and think they make perfect sense, especially with numeric fields. Suppose we have an "age" field, but don't actually know the age of the person, null makes perfect sense. Otherwise we'd have do do something like using a "magic number" like 0, -1, or a separate field altogether to indicate an unknown value. Granted, they do need some handling in queries.
- ilogik 7y agoexcept that age = 0 is valid if your database has toddlers. Hell, even -1 makes sense if you're running a query to get the age of someone at a specific time. this is why you should try to use NULLs whenever possible
- Andromeda88 7y agoI was dealing with NULLs whole day on MySQL workbench. It wasn't considering int as NULL value. Needed to make all empty cells 0 to be able to import data properly.
- wefarrell 7y agoNull 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 ago
- deleted 7y ago[deleted]
- michannne 7y agoAnother one is MIN and MAX ignore NULL values, which make for some interesting rollback scenarios. I also swear I have seen a gotcha involving UPDATE WHERE IN and not throwing an error where it should have, which is why I always quadruple check my update statements, but I wasn't able to reproduce it and couldn't find any information online. I haven't seen the issue in so long I forgot what it was, but it would update all rows in your table even if your WHERE clause was proper.
- oarabbus_ 7y agoWhat is a scenario where min or max should consider NULL values?
- robocat 7y agoAlso OR/AND can return non-null results even if NULL is one side of the operator: (NULL AND 0) gives 0 (0 AND NULL) gives 0 (NULL AND 1) gives NULL (1 AND NULL) gives NULL (NULL AND NULL) gives NULL (NULL OR 0) gives NULL (0 OR NULL) gives NULL (NULL OR 1) gives 1 (1 OR NULL) gives 1 (NULL OR NULL) gives NULL
- kords 7y agoDynamoDB, which is NoSql, also doesn't accept empty strings. But at least, Oracle automatically converts the empty string into NULL, comparing with DynamoDB which would actually fail the query.
- tabtab 7y agoA pet peeve of mine is concatenating null strings. It's like a poison pill that nulls the whole result. 99.99% of the time that's NOT what one wants domain-wise. Maybe the standard should make another concatenation operator that treats null strings as zero length strings. Sure, one can de-null each string in the expression, but that's ugly anti-DRY code. Please fix it, I haaate that.
- paulryanrogers 7y agoAgreed that it makes the pipe concat operator a lot less useful. Now PostgreSQL and MySQL both have CONCAT_WS which does replace NULLs with empty strings. It's also nice when you do need a common separator between all elements.
- irrational 7y agoWe recently moved from Oracle to Postgres. We had thousands of queries written based on the way Oracle handles NULLs and empty strings. It took us the better part of a year to rewrite all of them to the Postgres way. I am so glad to be off of Oracle.
- sashavingardt2 7y agoNow here's a blast from the past! 20 years ago this was common knowledge. Now it's making headline news on HN. SQL is back with the vengeance!
- paulryanrogers 7y agoWith some columnar databases NULLs are 'free' because they are a default, absent state or compressed away. Can be another reason to prefer them with very large datasets.
- altitudinous 7y agoI miss my past Oracle career, I've diagnosed this "= NULL" rather than "IS NULL" in so many broken queries, slow queries because of the way Oracle indexing handles NULL. There is a lot of discussion in this thread about whether this implementation of null checking in Oracle is appropriate, analysing it, but the current implementation is just fine, it has been tested by time. The internet does tend to rehash the same arguments over and over!!! The internet forgets. I remember these arguments 20 years ago.
- lisper 7y ago[Ignore this comment. It was posted by mistake. I'm only leaving it here for the historical record.] > the current implementation is just fine, it has been tested by time. No, it isn't "just fine". It is broken. Just because something has been broken for a very long time and has spawned an entire industry devoted to dealing with the fact that it is broken does not change the fact that it is broken.
- altitudinous 7y agoDo you have substantial experience with Oracle? or are you just blindly going on what everyone else says? There is no mention of outer joins in this thread, no mentions of the ability to minus results of one query from another which are basic constructs which handle many of the issues that are discussed here. It says that the people here are inexperienced with Oracle. Everyone here trying to resolve issues using inner joins. Inexperience. If people here had experience, not only would these topics have been discussed, but the real issues with NULL would have been discussed, one of which I mention in my previous post.
- lisper 7y agoSorry, I made a mistake: I thought I was responding to a different comment. (I'm currently on a very slow internet connection.) The "broken" thing I meant to refer to was Oracle's conflation of null and the empty string. But I think I hit the wrong "reply" link. Sorry about that.
- xivzgrev 7y agoNot sure what big deal is. You learn somewhere along the way that you check for null values with “is” vs “=“. Done, write it on a sticky note if you need, and move on. “Why isn’t it consistent??” - well a lot of systems have a lot of bat shit crazy inconsistencies, some times there for good reason. You learn to keep them straight and get your shit done. If you want to learn the “why” every time you encounter a system design quirk, be my guest but you may be going down a time intensive rabbit hole with little pay off for yourself.
- Jaxan 7y agoSometimes it’s easier to remember the why than the what. Then it does make sense to learn about it!
- hanche 7y agoNULLs in subselects do bite me with distressing regularity: Writing SELECT ... FROM ... WHERE blah NOT IN (SELECT foo FROM bar); getting no hits until I slap my forehead and add WHERE foo IS NOT NULL to the subselect.
- pjdorrell 7y agoTheoretically NULL means "unknown" value. As it happens, most business applications do not have any requirement to deal with "unknown" values. These applications are only interested in acting on requests where all the required data are provided by the person responsible for entering the data. For example, when I transfer money from one bank account to another, the amount of the transfer can't be "unknown", the sending account can't be "unknown", and the receiving account can't be "unknown". These same applications do have requirements to deal with empty values. Sometimes an empty value means "I haven't yet entered this value in the to the UI". But in that case the UI won't let you submit the form until you have supplied a valid value. In other cases an empty value is a valid value. For example, "who is your spouse?" and the answer is "I'm not married". Sometimes NULL represents "irrelevant", like for "who is your spouse?", where some of the records in the table represent people who can have spouses, and some of the records represent other person-like entities that aren't actually people and therefore they can't have spouses. Given that NULL is _not_ being used to represent "unknown" values, and there is a requirement to represent empty values, and you don't want to have a whole extra column just to represent "emptiness", the most straightforward way to implement empty values is to use NULL. So that is what happens. And you have to remember to use "is" instead of "=" when you want to test your empty NULL values for equality with other empty NULL values - because your SQL database is pretending that NULL really means "unknown", and it doesn't want to say that one unknown value is equal to another unknown value, because that would be theoretically incorrect.
- lesserknowndan 7y agoIn MySQL, NULL values are useful when using CONCAT_WS (concatenation with separator) or GROUP_CONCAT because NULL values will be ignored - so you don’t get e.g., “one,,two”.