10 ms·

SQL nulls are weird

214 points by subomi 2y ago

demurgos 2y ago
> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. 0: https://www.postgresql.org/docs/15/sql-createtable.html https://www.postgresql.org/docs/15/sql-createtable.html
- NoMoreNicksLeft 2y ago
  >also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?
  - stronglikedan 2y ago
    Simplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.
    - lcnPylGDnU4H9OF 2y ago
      Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?
      - demurgos 2y ago
        The main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.
        throwaway10235 2y ago
        I'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update
        homebrewer 2y ago
        It requires manual interventions because the upgrade process is basically dump + restore. MySQL and MariaDB upgrade between major versions automatically — you simply install the next version (or change the version tag in your container definition) and restart the server. Usually it takes almost no time, altought might be just as slow as PG when major changes to the data format are introduced. The only example I can remember is 8.0 when oracle completely rewrote the data format (making things like atomic ddl possible).
        panzi 2y ago
        You need to dump the database on the old PostgreSQL version and then load the dump on the new PostgreSQL version. Some other database servers can just use the old table data or migrate the data files on the fly transparently.
        Volundr 2y ago
        It's not client side, it's server side. The binary format between Postgres major versions is generally not compatible so you need to do a pg_dump from the old database and do a pg_restore into the new one. pg_upgrade [1] can let you bypass this by migrating the binary data directly, but still requires having both the new and old postgres versions installed. There's also things you can do with replication, but since we're talking simplicity I don't think that really applies. Personally I think upgrades are the one thing MySQL has on Postgres at this point. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
        marcosdumay 2y ago
        The GP is complaining about the need to run the upgrade cluster command, and reconfigure your server when you want to use a different version of postgres itself. MySql does it transparently if you just open the database in the new server. Of course, lots of people also think it's a feature. But those aren't very vocal. Anyway, this is a main reason why people keep using old postgres versions, they have to think about upgrading, and they often don't.
      - phplovesong 2y ago
        Usually its a skill issue
      - badlibrarian 2y ago
        VACUUM
        kstrauser 2y ago
        If your data's large and changing enough that you have to care about vacuuming, any reasonable database is going to require some tuning, tending and management. I'd posit that only a tiny fraction of PostgreSQL uses have to know or care that vacuuming is a thing because the autovacuum default handle it for them.
        mardifoufs 2y ago
        Sure, it's never going to be plug and play, but it doesn't mean that all the issues will be equivalent. Vacuuming doesn't really have an equivalent in say, MySQL. It's something you don't have to worry about if you use the latter. For example, HA and clustering will always be challenging to deploy/maintain, but you will still have a harder time doing that with postgres than with MySQL. Postgres also has a lot of benefits obviously, though.
      - deleted 2y ago
        [deleted]
    - homebrewer 2y ago
      Also the reverse — MySQL et al support much more complex replication topologies out of the box, including multi master for the very rare use case when you need it. It's also much easier to tune, most database instances require setting innodb_buffer_pool_size, and that's basically it. Newer versions can even set it automatically if you're fine with consuming all memory on that machine, thus requiring no tuning at all.
  - password4321 2y ago
    Replication
  - poincaredisk 2y ago
    * legacy applications or vendor lock-in * use of a cloud provider that favours another database (like SQL server) * some people claim mysql/maria is faster for them, simpler, or has a better support for replication * use of sqlite for lightweight or portable apps * domain specific databases that still use sql as their query language * someone may want to use another database for fun or to learn something new
  - hot_gril 2y ago
    SQLite has its own use cases. And MySQL was all-around better than Postgres in the past, so it still has more usage in some areas. Nowadays I'll take Postgres over MySQL by default, but it's not a huge difference.
- duncan-donuts 2y ago
  Introducing “unknown” feels like another kind of hell like undefined in JavaScript.
  - demurgos 2y ago
    Just to clarify, I'm not advocating to introduce a new `unknown` keyword. I'm saying that the existing `null` in SQL was not named properly and that the name `unknown` would have been more fitting. SQL's `null` already has the semantics of `unknown` as explained in the part of the article that I quoted.
    - wvenable 2y ago
      SQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.
      - demurgos 2y ago
        Really? I know that SQL is old but I would have expected `null` to refer to pointers at first. Going by Wikipedia, I see that SQL is from 1974 and C from 1972. Were there earlier uses/drafts where `null` is "unknown" instead of "unset"?
        wvenable 2y ago
        I wouldn't necessarily define `null` as "unknown" -- it's just "no value" -- which is really the same thing and also somewhat equivalent to "unset". But null pointers aren't unset as pointers aren't initialized to null in C and you can explicitly set a pointer to null. E.F. Codd added nulls to relational model in 1970 so that does pre-date C. The concept is even older than that I imagine.
        recursive 2y ago
        In nth normal form, you can't have 'no value'. That would mean your model is wrong. In academic relational data books, null does mean "unknown". There is a value, we just don't know what it is (yet). If there might actually not be such a value, you're supposed to change your schema to reflect that.
- magicalhippo 2y ago
  The result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN. That said, as someone self-taught in SQL, I agree NULL was not a good choice. Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better. [1]: https://stackoverflow.com/a/79270181 https://stackoverflow.com/a/79270181 [2]: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/language-element... [3]: https://www.postgresql.org/docs/current/functions-comparison.html https://www.postgresql.org/docs/current/functions-comparison...
  - otteromkram 2y ago
    Also self-taught SQLer and I don't have an issue with NULL. I also don't use UNIQUE constraints, so maybe that has something to do with it.
    - deleted 2y ago
      [deleted]
    - magicalhippo 2y ago
      I don't have an issue as such, I was a fairly experienced developer first time I had to dabble with SQL, but sometimes it can still surprise. For example I learned the hard way that the DB we use at work does not index NULL values. And once in a while if I'm tired or stressed I might forget about UNKNOWN and thus that "Col <> 42" does not return rows where Col is NULL. Not that better naming would prevent such surprises, but I still think the current naming is less than optimal from a pedagogical perspective. At least I see this at times when teaching our support folks SQL (many have domain background and not a technical background).
  - labster 2y ago
    SQL was developed in the 1970s, there’s no way they’d waste all those bytes to spell out UNKNOWN and INDETERMINATE.
    - 2y ago
- layer8 2y ago
  SQL NULL doesn’t behave like “unknown” in all contexts. That’s one issue of NULL, that it doesn’t really have consistent semantics.
  - masklinn 2y ago
    Furthermore if null only means unknown then we need a value for “known absent”, there’s a reason why null is so often used as that.
    - thaumasiotes 2y ago
      Do you actually need that in a Boolean context? It would only be useful for evaluating self-referent claims like "this sentence is false".
      - masklinn 2y ago
        Your questions might be relevant if null were limited to boolean contexts. It’s not.
    - int_19h 2y ago
      From a purely relational perspective, if some piece of data can be absent, it's a 1:N relation where N<=1, and ought to be encoded as such. (Of course, this is rather awkward in practice, and when NULL is there, it's inevitably going to be used for that instead.)
      - feoren 2y ago
        It is encoded as such. That's why most columns are made nullable. It's crazy to say you need to use the full power of a 1:N relation with some child table when you know N cannot be greater than 1, when a nullable column already exactly encodes a 1:(0..1) relation. I'm not trying to shill for null here: one of null's great problems is exactly the fact that null can represent "unknown", "known absent", "not applicable", or even some sentinel-ish "other" escape condition, each with their own subtle differences in how they should be handled. Null has tons of problems, of course. But it's patently absurd to claim that you "ought to be" making a different 1-column table with a unique foreign key or shared primary key for every column that might be absent, because of some vague appeal to the fact that you can write it as a 1:N relation with N<=1. You can just as easily claim that every non-nullable column is a 1:N relation where N==1 and "should be encoded as such". It is encoded as such! That's what a column is!
- cm2187 2y ago
  The problem is that in practice in a database NULL is a placeholder for a missing value, not an unknown value.
  - bballer 2y ago
    And to further apply semantics to this just to be snide, the value is only "missing" if it could possibly ever be defined for that tuple. There are cases where you expect the value to be "missing", and it not being "missing" would be considered a data integrity issue. Fun.
    - cm2187 2y ago
      Yes I should have rather written “an absence of data”. But still not “unknown”.
  - SoftTalker 2y ago
    Best way to think of NULL is "no value." Not "unknown value," as that implies that it is a value you just don't know what it is. Not "missing" value as that even the notion of a value being "missing" tells you something. NULL is no value. It's like a black hole, it consumes anything it touches. Any expression involving a NULL term becomes NULL.
jmyeet 2y ago
NULL is the absence of a value. If you try and treat it as a value, you're going to have a bad time. So an attempted UNIQUE(email_address, deleted_at) constraint is fundamentally flawed. If you treated NULL as a value that could be unique, you're going to break foreign keys. But let's continue the logic of deleted_at being NULL indicating an active account, which seems to the intent here. You end up doing things like: SELECT /* ... */ FROM accounts WHERE email_address = '...' AND deleted_at IS NOT NULL Depending on your database, that may or may not index well. More problematic, you may end up with privacy leaks if someone forgets the last conditional. If anything, you want to reverse this so someone has to go out of their way to explicitly select deleted accounts. There are multiple strategies for this eg using an active_accounts view or table. Lastly, there are lots of potential reasons for an account to be disabled or otherwise not visible/accessible. Takedowns, court orders, site safety, hacked accounts and so on. Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design.
- indeed30 2y ago
  That's interesting - I believe this is exactly how Sequelize implements soft-deletion.
- giraffe_lady 2y ago
  You put the "is not null" on the index itself and then simply don't use it for the much rarer queries that are on deleted accounts. Or just use a view for active accounts. Overloading timestamps to carry a boolean on null is awesome as long as you decide that's what you're doing and use one of the several standard techniques to dodge the easily avoided potential downside. This isn't a valid security concern, more than any other incorrect sql query would be anyway. A dev can always write a bad Q, you need another way to address that it's not more likely here because of the null.
- chuckadams 2y ago
  > Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design. Then don't do that. It's kind of a leap to say soft deletes are categorically bad because someone might confuse "deleted" with "inactive". My users table does the super-advanced thing of having both columns. The ORM also doesn't forget to add the not-null criterion. There's also zero databases in active use where it poses a problem to indexing. Soft deletes suck in their own way, but none of the alternatives are perfect either.
zokier 2y ago
SQL nulls in some ways behave in similar to floating point nans. Of course nans are also weird in their own way, but it is a bit comforting that its not so completely singularly weird.
- dunham 2y ago
  Also similar to the bottom value in haskell and exceptions in other languages.
- giraffe_lady 2y ago
  NaN is cool because it's almost like a type that constrains uncertainty. What do we know about this entity? not much! but it's definitely not a number. Calling it anything else would have been a mistake. Null is more confusing because it means different things in different languages. Sometimes it's a more constrained uncertainty, eg this definitely doesn't exist. But in sql it's a less constrained uncertainty, like "undefined" in math. The value of this thing couldn't make sense in this context, but we can't make assertions about its existence.
  - mplanchard 2y ago
    There's another comment in here that talks about thinking of NULL as UNKNOWN, and I quite like that. It makes a lot more sense, and I think it would've been a better choice to standardize on. UNDEFINED would also be an improvement.
    - reshlo 2y ago
      UNDEFINED would not be accurate. If your signup form has an optional field for a full name which I don’t fill in, I still have a name. Just because a value is not known by your database doesn’t mean it isn’t defined. E. F. Codd thought about this issue.[0] > Codd indicated in his 1990 book The Relational Model for Database Management, Version 2 that the single Null mandated by the SQL standard was inadequate, and should be replaced by two separate Null-type markers to indicate why data is missing. In Codd's book, these two Null-type markers are referred to as 'A-Values' and 'I-Values', representing 'Missing But Applicable' and 'Missing But Inapplicable', respectively. Codd's recommendation would have required SQL's logic system be expanded to accommodate a four-valued logic system. Because of this additional complexity, the idea of multiple Nulls with different definitions has not gained widespread acceptance in the database practitioners' domain. It remains an active field of research though, with numerous papers still being published. [0] https://en.wikipedia.org/wiki/Null_(SQL) https://en.wikipedia.org/wiki/Null_(SQL)
irrational 2y ago
I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.
- svieira 2y ago
  Domain-embedded nulls are the bane of my existence.
- hyperman1 2y ago
  Oh man. I had a talk with a DBA about how oracle could not deal with an adress with no street name - literally a tiny village with 10 houses on 1 nameless town square. It was unsearchable in parts of the app because street='' was interpreted as street is null. DBA kept claiming oracle was right and the town should adapt their naming to our software. This attitude was so prevalent at the time, I sometimes wonder if the rise of noSQL was simply people sick of dealing with Oracle DBAs
  - aidenn0 2y ago
    > This attitude was so prevalent at the time, I sometimes wonder if the rise of noSQL was simply people sick of dealing with Oracle DBAs That was definitely one part; another part was sharp corners in MySQL (at least as of 20 years ago; I would be surprised if many of them haven't been rounded off in the meantime). The last part was places with no DBA with developers unaware of how to handle schema migrations.
- zo1 2y ago
  It's weirder. If you insert an empty string into a VARCHAR field in Oracle, it returns Null back to you when you query that same field. At the very least, I'd expect a software system to behave in a deterministic way. I.e. either throw an error because you're not doing something right (whatever Oracle deems right in this case), or give you back what you gave it, especially for database software who's entire role of existence is to persist data without side-effects.
datadrivenangel 2y ago
SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.
- grahamlee 2y ago
  Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.
  - setr 2y ago
    The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. The use of x = NULL instead of x IS NULL is pretty much always a mistake. More importantly, x = value instead of (x = value and x IS NOT NULL) is almost always a mistake, and a stupidly subtle one at that. And for this curse, we get… nothing particularly useful from these semantics. Also the x != NULL case is completely cursed
    - grahamlee 2y ago
      > The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. It's a three-valued logic (though not trinary, which would use a base-3 number system) in a three-valued algebra: specifically, the relational algebra. The outcome of a logical test has three values: true, false, or NULL; this is distinct from Boole's algebra where outcomes have a continuous value between 0 and 1 inclusive.
  - tzs 2y ago
    That's not the only time it is weird. There's even a whole book by one of the pioneers of the relational DB model, Date's "Database Technology: Nulls Considered Harmful" [1], covering many of the ways it is weird. [1] https://www.amazon.com/Database-Technology-Nulls-Considered-Harmful/dp/1634624769 https://www.amazon.com/Database-Technology-Nulls-Considered-...
ungut 2y ago
The NULLs in unique constraints quirk actually works differently in ORACLE databases, which is infuriating to say the least. Apparently this comes from some ambiguity in some sql standard, anyone know more about this?
- MathMonkeyMan 2y ago
  All I know is from this SQLite article: <https://www.sqlite.org/nulls.html https://www.sqlite.org/nulls.html>
al2o3cr 2y ago
FWIW, you can explicitly change this behavior in Postgres as of version 15 - include "NULLS NOT DISTINCT" when creating the unique index.
hiAndrewQuinn 2y ago
SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNKNOWN = UNKNOWN, because it could darn near be anything: TRUE XOR TRUE, TRUE XOR FALSE, FALSE XOR FALSE, FALSE XOR TRUE... Internalizing this is where SQL's use of NULL / UNKNOWN really becomes intuitive. 4. (TRUE AND FALSE) XOR (TRUE OR UNKNOWN) = (FALSE) XOR (TRUE) per #1 = TRUE. See, it's consistent, you just need to keep in mind that if you have a lot of known UNKNOWNs they're quite parasitic and your final outcome is likely to be, itself, an UNKNOWN. Just like in real life!
- thayne 2y ago
  If only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.
  - dominicrose 2y ago
    Javascript has both null and undefined and I'm not sure that's a good idea. At least in SQL we only have one of them, but it can mean unknown or it can mean N/A or even false. It's like a joker, what it means depends on how you use it.
    - hun3 2y ago
      Or VBA, which has Empty, Null, and Nothing: https://excelbaby.com/learn/the-difference-between-empty-null-and-nothing-in-vba/ https://excelbaby.com/learn/the-difference-between-empty-nul... (and sometimes Missing)
    - hobs 2y ago
      No, it's not those other things, that's just using the tool incorrectly. A NULL is definitely "we dont know", not false, not N/A, especially not any known value.
bunderbunder 2y ago
> ... and this is even less obvious if you’re used to using ORMs. Which is why I continue to be such an ORM skeptic. I agree that they're convenient. But I do worry that we've now got an entire generation of engineers who regularly interact with relational databases, but have largely been spared the effort of learning how they actually work. As another commenter pointed out, if you've learned basic relational algebra then the way SQL nulls behave seems obvious and logically consistent. The logic is the same as the logic behind the comparison rules for NaN in IEEE floats. It's the behavior of C-style nulls that is, always and forever, a billion-dollar mistake.
- thrance 2y ago
  My experience with ORMs is that most of the time you end up needing to write some queries by hand, in raw SQL. Usually these are the most complex, that you can't express in your ORM's DSL. My point being, I don't think using an ORM really shields you from having to learn how it works behind the scenes.
  - globular-toast 2y ago
    It's not even about having to write SQL by hand. In an ORM like Django that's exceedingly rare. But you still need to understand what's going on underneath. In other words, it's the most leaky abstraction there is. I think the popularity is mostly aesthetic and convenience. Most people into ORMs like Django don't really know about layered architecture and that you can keep all your SQL in one place in the data access layer. They just scatter that stuff everywhere in the codebase.
    - feoren 2y ago
      I don't know Django specifically but I'm always floored by how people talk about ORMs. They're only a leaky abstraction if you believe their point is to shield terrified junior devs of the inner workings of the scary relational database. That's an awful way to use ORMs, and the source of most of the flak they get. To be fair, some are designed that way, or at least strongly push you toward it. Stop thinking of ORMs as trying to hide the details of SQL and you'll stop hating them. Instead think of them as a way to compose SQL dynamically, with the full power of your language. SQL is an awful language to write application logic in, because it has horrible support for abstraction, composition, encapsulation, dependency injection, etc. The ORM gives you a way to produce SQL in an environment that actually supports basic software engineering principles. Scattering ORM logic everywhere in the codebase is the point: putting all your SQL in one data access layer is like putting all your arithmetic in one calculation layer. Why would you ever do that? What's wrong with seeing a plus sign in more than one file? What's wrong with seeing language-encoded relational logic in more than one file? I can guarantee you the popularity is not "aesthetic". And convenience is a real thing that actually does reduce costs. People complain about ORMs, but have you seen the absolute horse-shit-level code that people jam into SQL functions and procedures to do the utterly most basic things? The standard for what ends up in SQL Stored procedures is the most unmaintainable garbage in the entire software engineering ecosystem.
kurtbuilds 2y ago
If you want equality testing with nulls, you want to use `is (not) distinct from` instead of `=` and `<>` / `!=`. `1 is not distinct from NULL` => false `NULL is not distinct from NULL` => true `0 is not distinct from 1` => false
- blast 2y ago
  Having that is much better than not having it, but man is it verbose and confusing.
Recursing 2y ago
Surprised that this doesn't mention "IS DISTINCT FROM" ( https://modern-sql.com/caniuse/is-distinct-from https://modern-sql.com/caniuse/is-distinct-from ) (Although in rare cases that is even weirder: https://stackoverflow.com/a/58998043 https://stackoverflow.com/a/58998043 )
- ziml77 2y ago
  I'm glad SQL Server finally got this, but I wish the syntax was nicer. It's a multi-word infix operator that gets tough to read. I've been using Snowflake SQL recently and I like that they just made it a function called EQUAL_NULL
osigurdson 2y ago
Weirder still are floating point numbers in SQL.
tzury 2y ago
For Postgres specific approach, you may refer to https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null https://blog.rustprooflabs.com/2022/07/postgres-15-unique-im... Practically speaking, I go with not null, and always set default value.
exabrial 2y ago
null != null is pretty bizarre at first, until you understand the reason the did it was to try to make sense of null-able indexed columns. Not sure why we couldnt have our cake and eat it, but instead we got IS NOT NULL is not the same as != NULL
otteromkram 2y ago
There's another good, technical write-up on NULL behavior in SQL at modern-sql.com https://modern-sql.com/concept/null https://modern-sql.com/concept/null (Note: I am not affiliated with that bloh/website in any way, shape, or form.)
lolpanda 2y ago
I actually like how NULLs behave in SQL. They mean "I don't know" In the modern programming language we all care about Null safety. But no matter how you model your data, you will always run into the situations when you don't know everything. So I believe NOT NULL is not very practical. NULLs in SQL handle these case very well - when the input is unknown your output is unknown
- int_19h 2y ago
  Except they don't consistently behave that way. If NULL means "unknown", why do they show up in outer joins, or when you SUM an empty table?
  - cglace 2y ago
    The most annoying is having to order by DESC NULLS LAST to get the largest value from an aggregation.
- dalton_zk 2y ago
  I feel like the same, Null equal null is null is totally right
  - afiori 2y ago
    I feel like a select for: - col1 = 1 should not return NULLS - !(col1 = 1) should return NULLS - col1 <> 1 should not return NULLS
- kijin 2y ago
  Agreed. If SQL didn't have NULL, we'd have other special values meaning "I don't know" or "no data" all over the place. Too many newbies hear that NULL is bad, so they declare all columns as NOT NULL and end up inserting ad hoc values like 0, -1, '', or {} when they inevitably come across cases where they don't have data. Which is even worse than NULL.
gxt 2y ago
This has always made queries unpredictable in many scenarios and it should be a feature to turn nulls off entirely and swap them out with Option<T> instead.
- solumunus 2y ago
  How would you handle unmatched outer joins?
  - masklinn 2y ago
    a left outer join b yields tuples of (A, Option<B>), a full outer join b yields tuples of (Option<A>, Option<B>)
  - benzayb 2y ago
    By having a default value (non-null) for each declared type of those columns. Or, the user must define a default value in the query itself. Yes, tedious; but, precise and forces the programmer to really prepare for the "unknown" scenario.
galaxyLogic 2y ago
In Object Oriented Context "null" is useful to indicate that some object doesn't have value for that property. What's interesting is, do we mean that in our data that attribute has no value? Or do we mean the real-world object represented by the data does not have that attribute? Does null mean a) We don't know the value of this attribute for this object, or b) We do know that there is no value for this attribute in the real-world object represented by our data. In JavaScript because there is both null and undefined it is easy to assume that undefined means we don't know the value and null means we do know it has no value. EXAMPLE: The attribute 'spouse'. Some people have a spouse some don't. So what does it mean if the value of the field 'spouse' is null? That we know there is no spouse, or that we don't know who the spouse is if any. In practical terms we can say null means "We don't know" which includes the case that there is no spouse.
- andai 2y ago
  I remember from my databases course at university that NULL means that the database doesn't contain that data, and empty string means that it is known to be empty.
  - niij 2y ago
    What is the type is something other than a string? age: null? married: null?
  - feoren 2y ago
    That's your professor's opinion, and probably one that does not come from industry experience. Look in 4 different databases and you'll see 9 different conventions. A common one is to have all strings non-null with a default value of empty string. And not all columns are strings; there is no "obviously empty" integer or boolean.
  - zo1 2y ago
    Let's also all be reminded about how Oracle DB doesn't let you insert empty strings, and instead treats them as NULLS even if you gave it an empty string initially. https://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null https://stackoverflow.com/questions/203493/why-does-oracle-9... That was a fun bug to find out, after having dealt with quite a few other DBs over the years. It was one of those "No, but surely" and "This can't be! This is Oracle!" moments. Found it while porting some old code that needed to store an empty string as being distinct from a NULL in that same column.
khana 2y ago
[dead]
branko_d 2y ago
NULLs are weird because they are basically two different types under the same name. The 3-value logic type is useful for representing "missing" foreign keys, but 2-value logic type is arguably more useful when searching/sorting/aggregating. I think we would have been better-off by treating FKs (and maybe outer JOINs) as a special case, and using 2-value logic everywhere else.
hot_gril 2y ago
Weird as they seem at first, SQL null handling ends up being convenient the way it is. Part of this is because left/right join give you nulls.
qwertydog 2y ago
SQL NULL is also coerced to different boolean values depending on context e.g. in a WHERE clause NULL is coerced to false, whereas in a CHECK constraint NULL is coerced to true https://dbfiddle.uk/C5JqMP8O https://dbfiddle.uk/C5JqMP8O
wruza 2y ago
I think (blasphemous hot take ahead) that the standards of implementation of relational models are wrong. NULLs still have their (rare) place, but the foremost issue with query results is that they are tabular rather than hierarchical. The main culprits being (1) outer joins that represent or induce nonsensical operations and (2) lack of non-null “zero” values for types like date. Of course hierarchies can make querying more complex, but mostly in cases where the relational logic goes crazy itself and you had to go tabular anyway. If you think of it, distinct, group by and windowing feel like workarounds in tabular mode but would be natural to hierarchies, because everything is naturally distinct and grouped-by by design and windows are basically subtables in these rows. Bonus points you could fetch “SELECT FROM a, b_rows LEFT JOIN b AS b_rows …” in a single query without duplicating `a`s and nullifying `b`s when N <> 1. And when you aggregate through a column in `b`, there’s no headache what to do with join-produced NULLs (unless `b` columns are nullable by your design, then it’s on you). And when it all arrives to a client, it’s already well-shaped for ui, processing, etc. No more: last_a_id = undefined for (row of rows) { if (row.id != last_a_id) { … last_a_id = row.id } … } I’m pretty sure you recognize this programming idiom immediately. Before you criticize, I’m not talking about hierarchical/OO tables. Only about ways of getting and handling query results. You still can reshape a relation like you want. The difference is that a database engine doesn’t have to put it all onto a (N x M x …) table and instead creates sort of a subset of relations which is efficient in space and natural to walk through. It already does that when walking through indexes, selects are naturally hierarchical. All it has to do is to track relations it went through rather than just dumping rows from a set of cursors that it knows the start and end points of, but loses this knowledge by writing into a plain table.
iefbr14 2y ago
When the null concept was introduced to me in the seventies, the only thing I could say was that it would be causing a lot of unnecessary confusion in the future. If you have missing values in your datarecord then that datarecord belongs in an exception-queue. And now some 45 years later people are still discussing it like we did then..
- acuozzo 2y ago
  > If you have missing values in your datarecord then that datarecord belongs in an exception-queue. This depends on the context, no? I doubt there exists someone with a contact list on their phone which has every single field for every single contact populated. There needs to be some way to codify that a field in a datarecord is unpopulated. Using the "zero value" for the type of the field (e.g., the empty string) is reasonable, but is this necessarily better than NULL? I reckon an argument can be made that this approach is just as likely to lead to bugs. I'm not necessarily in favor of NULL, for what it's worth, but I can't think of an adequate replacement which doesn't reduce to "NULL in sheep's clothing".
  - thfuran 2y ago
    I did last week, before I added the first contact.
- jfb 2y ago
  Sometimes you want UNKNOWN, sometimes you want MISSING.
  - iefbr14 2y ago
    Just give the data item a status field, don't fix it by medling with a designated (non)value. And while you are at it you can add some valid-from and valid-to fields for the item. That's how you do it proper.
- DangitBobby 2y ago
  Missing values are not always an exception. There's a reason modern languages almost universally include an Option type which may contain a Null and allow you to pass it around as a first class value. Good data representations allow you to express missing values.
at_a_remove 2y ago
I have deep but vague thoughts around the concept. My first intuition is that we have put too many things under NULL and None and such. Partially, we use ... and I'll be very broad here ... "variables" as boxes we look in for answers. Answers to questions, answers to "Hey I put something in there for the time being to reference later." If I went into programming terms rather than just SQL, sometimes we get meta-answers. You haven't made the box yet (declared the variable). You haven't decided how the box is structured (picked a type or a length or something). Okay, you did those but the box is virgin (nothing has been placed in the box yet). That kind of thing. An empty set for "yes, you asked but nothing meets those criteria."
kopirgan 2y ago
Recall this really funny dialogue in one of the Blackadder episodes. The princess eyes are as blue as the stone of Galveston Have you seen the princess eyes? No! Have you seen the blue stone of Galveston? No! So you're comparing something you've never seen with something else you've never seen! That's NULL comparison
xd 2y ago
To me, "Unknown" almost implies the possiblity of a value, whereas I've always thought of NULL as being an absence of a value. edit: an empty string, false, 0 are all values.
getnormality 2y ago
I don't see why this is weird. Unique means no duplicates. Nulls can't be duplicates of each other because they're not equal to each other. If you don't like null semantics, you're free to use sentinel values. You can make all the sentinel values the same, or you can make them all different. Either way, you or someone who has to use your system will be back here tomorrow complaining about how weird it is.
trollbridge 2y ago
And SQL null shares the feature with many other languages that any type can be a NULL (although a column can be set NOT NULL). Much like Java, it is no end of grief that a type that claims to be, say, a “NUMBER” is actually “NUMBER | NULLType”.
drzaiusx11 2y ago
Ah yes, someone discovering the existence of three value logic in SQL and expecting 2VL behavior. Classic. We've all been there, right? Personally I wish more languages were like python or ruby and had chosen None or Nil over Null which alleviates the confusion a bit, as those names better indicates that it's NOT an "unknown" (1 unknown value != 1 other unknown, which intuitively makes sense.) In ruby or python it's more obvious that None and Nil are "nothing" types and therefore equivalence makes sense (nil == nil, None == None are both true)
criloz2 2y ago
It is not supposed that null is the bottom value in the universe of all the values that your program can recognize? Why people need to complicate it?, and yeah in that definition `null == null`, but a `null_pointer != null` because null pointer is at the bottom of all the possible pointer value, and null by itself is not a pointer. The same for (0,null), (false, null) and ("", null). null should only be equal to itself. And lastly undefined != null, because undefined is related with structures indicating that a field was not defined when the structure was created
_zagj 2y ago
The simplest end-run around this is to avoid NULLs entirely, which normalization (even just the first normal form) requires.
ludwik 2y ago
Even if we set three-value logic aside for a moment, this behavior of NULL still makes sense intuitively. The value of NULL in a particular table cell is simply a way to indicate 'no value'. If you want the values in a column to be unique, cases where there are no values shouldn't be considered. This plays out similarly in practice. For example, you may want to allow users to optionally reserve a username, and if they do, those usernames should be unique. It's hard to imagine a use case where by wanting a field to be both optional (nullable) and unique, you mean that the field should be optional for a single record (!) and required for all the rest. Of course, you mean that IF there is a value, THEN it should be unique.
boxed 2y ago
All of this would be avoided if NULL in sql was just called "UNKNOWN". Which is what it is. Terrible name :/ Imo, SQL should add "NOTHING", add "UNKNOWN" as a synonym for "NULL", and deprecate "NULL".
whartung 2y ago
This reminds me back in the day when I was writing a DSL for a project. Since the data we were getting was sourced from an RDBMS, I wanted NULL to be a first class concept in the DSL, with similar traits. Early on, I simply made any expression that involved a NULL result in NULL. Naively this was all well and good, but it failed spectacularly in condition statements. Instead of A = NULL == false, I had A = NULL == NULL. And, as you can imagine, a single NULL in the expression would just pollute the entire thing, and since NULL was considered as FALSE for conditionals, any NULL in an expression made the entire thing, eventually, FALSE. Naturally I went back and made the comparison operators always return booleans. But it was a fun little side effect at the time.
jakubmazanec 2y ago
That's why I like EdgeDB and EdgeQL: no null [1] [1] https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql

214 points by subomi 2y ago

> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. 0: https://www.postgresql.org/docs/15/sql-createtable.html https://www.postgresql.org/docs/15/sql-createtable.html

NoMoreNicksLeft 2y ago
>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?
- stronglikedan 2y ago
  Simplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.
  - lcnPylGDnU4H9OF 2y ago
    Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?
    - demurgos 2y ago
      The main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.
      - throwaway10235 2y ago
        I'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update
        homebrewer 2y ago
        It requires manual interventions because the upgrade process is basically dump + restore. MySQL and MariaDB upgrade between major versions automatically — you simply install the next version (or change the version tag in your container definition) and restart the server. Usually it takes almost no time, altought might be just as slow as PG when major changes to the data format are introduced. The only example I can remember is 8.0 when oracle completely rewrote the data format (making things like atomic ddl possible).
        panzi 2y ago
        You need to dump the database on the old PostgreSQL version and then load the dump on the new PostgreSQL version. Some other database servers can just use the old table data or migrate the data files on the fly transparently.
        Volundr 2y ago
        It's not client side, it's server side. The binary format between Postgres major versions is generally not compatible so you need to do a pg_dump from the old database and do a pg_restore into the new one. pg_upgrade [1] can let you bypass this by migrating the binary data directly, but still requires having both the new and old postgres versions installed. There's also things you can do with replication, but since we're talking simplicity I don't think that really applies. Personally I think upgrades are the one thing MySQL has on Postgres at this point. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
        marcosdumay 2y ago
        The GP is complaining about the need to run the upgrade cluster command, and reconfigure your server when you want to use a different version of postgres itself. MySql does it transparently if you just open the database in the new server. Of course, lots of people also think it's a feature. But those aren't very vocal. Anyway, this is a main reason why people keep using old postgres versions, they have to think about upgrading, and they often don't.
    - phplovesong 2y ago
      Usually its a skill issue
    - badlibrarian 2y ago
      VACUUM
      - kstrauser 2y ago
        If your data's large and changing enough that you have to care about vacuuming, any reasonable database is going to require some tuning, tending and management. I'd posit that only a tiny fraction of PostgreSQL uses have to know or care that vacuuming is a thing because the autovacuum default handle it for them.
        mardifoufs 2y ago
        Sure, it's never going to be plug and play, but it doesn't mean that all the issues will be equivalent. Vacuuming doesn't really have an equivalent in say, MySQL. It's something you don't have to worry about if you use the latter. For example, HA and clustering will always be challenging to deploy/maintain, but you will still have a harder time doing that with postgres than with MySQL. Postgres also has a lot of benefits obviously, though.
    - deleted 2y ago
      [deleted]
  - homebrewer 2y ago
    Also the reverse — MySQL et al support much more complex replication topologies out of the box, including multi master for the very rare use case when you need it. It's also much easier to tune, most database instances require setting innodb_buffer_pool_size, and that's basically it. Newer versions can even set it automatically if you're fine with consuming all memory on that machine, thus requiring no tuning at all.
- password4321 2y ago
  Replication
- poincaredisk 2y ago
  * legacy applications or vendor lock-in * use of a cloud provider that favours another database (like SQL server) * some people claim mysql/maria is faster for them, simpler, or has a better support for replication * use of sqlite for lightweight or portable apps * domain specific databases that still use sql as their query language * someone may want to use another database for fun or to learn something new
- hot_gril 2y ago
  SQLite has its own use cases. And MySQL was all-around better than Postgres in the past, so it still has more usage in some areas. Nowadays I'll take Postgres over MySQL by default, but it's not a huge difference.
duncan-donuts 2y ago
Introducing “unknown” feels like another kind of hell like undefined in JavaScript.
- demurgos 2y ago
  Just to clarify, I'm not advocating to introduce a new `unknown` keyword. I'm saying that the existing `null` in SQL was not named properly and that the name `unknown` would have been more fitting. SQL's `null` already has the semantics of `unknown` as explained in the part of the article that I quoted.
  - wvenable 2y ago
    SQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.
    - demurgos 2y ago
      Really? I know that SQL is old but I would have expected `null` to refer to pointers at first. Going by Wikipedia, I see that SQL is from 1974 and C from 1972. Were there earlier uses/drafts where `null` is "unknown" instead of "unset"?
      - wvenable 2y ago
        I wouldn't necessarily define `null` as "unknown" -- it's just "no value" -- which is really the same thing and also somewhat equivalent to "unset". But null pointers aren't unset as pointers aren't initialized to null in C and you can explicitly set a pointer to null. E.F. Codd added nulls to relational model in 1970 so that does pre-date C. The concept is even older than that I imagine.
        recursive 2y ago
        In nth normal form, you can't have 'no value'. That would mean your model is wrong. In academic relational data books, null does mean "unknown". There is a value, we just don't know what it is (yet). If there might actually not be such a value, you're supposed to change your schema to reflect that.
magicalhippo 2y ago
The result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN. That said, as someone self-taught in SQL, I agree NULL was not a good choice. Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better. [1]: https://stackoverflow.com/a/79270181 https://stackoverflow.com/a/79270181 [2]: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/language-element... [3]: https://www.postgresql.org/docs/current/functions-comparison.html https://www.postgresql.org/docs/current/functions-comparison...
- otteromkram 2y ago
  Also self-taught SQLer and I don't have an issue with NULL. I also don't use UNIQUE constraints, so maybe that has something to do with it.
  - deleted 2y ago
    [deleted]
  - magicalhippo 2y ago
    I don't have an issue as such, I was a fairly experienced developer first time I had to dabble with SQL, but sometimes it can still surprise. For example I learned the hard way that the DB we use at work does not index NULL values. And once in a while if I'm tired or stressed I might forget about UNKNOWN and thus that "Col <> 42" does not return rows where Col is NULL. Not that better naming would prevent such surprises, but I still think the current naming is less than optimal from a pedagogical perspective. At least I see this at times when teaching our support folks SQL (many have domain background and not a technical background).
- labster 2y ago
  SQL was developed in the 1970s, there’s no way they’d waste all those bytes to spell out UNKNOWN and INDETERMINATE.
  - 2y ago
layer8 2y ago
SQL NULL doesn’t behave like “unknown” in all contexts. That’s one issue of NULL, that it doesn’t really have consistent semantics.
- masklinn 2y ago
  Furthermore if null only means unknown then we need a value for “known absent”, there’s a reason why null is so often used as that.
  - thaumasiotes 2y ago
    Do you actually need that in a Boolean context? It would only be useful for evaluating self-referent claims like "this sentence is false".
    - masklinn 2y ago
      Your questions might be relevant if null were limited to boolean contexts. It’s not.
  - int_19h 2y ago
    From a purely relational perspective, if some piece of data can be absent, it's a 1:N relation where N<=1, and ought to be encoded as such. (Of course, this is rather awkward in practice, and when NULL is there, it's inevitably going to be used for that instead.)
    - feoren 2y ago
      It is encoded as such. That's why most columns are made nullable. It's crazy to say you need to use the full power of a 1:N relation with some child table when you know N cannot be greater than 1, when a nullable column already exactly encodes a 1:(0..1) relation. I'm not trying to shill for null here: one of null's great problems is exactly the fact that null can represent "unknown", "known absent", "not applicable", or even some sentinel-ish "other" escape condition, each with their own subtle differences in how they should be handled. Null has tons of problems, of course. But it's patently absurd to claim that you "ought to be" making a different 1-column table with a unique foreign key or shared primary key for every column that might be absent, because of some vague appeal to the fact that you can write it as a 1:N relation with N<=1. You can just as easily claim that every non-nullable column is a 1:N relation where N==1 and "should be encoded as such". It is encoded as such! That's what a column is!
cm2187 2y ago
The problem is that in practice in a database NULL is a placeholder for a missing value, not an unknown value.
- bballer 2y ago
  And to further apply semantics to this just to be snide, the value is only "missing" if it could possibly ever be defined for that tuple. There are cases where you expect the value to be "missing", and it not being "missing" would be considered a data integrity issue. Fun.
  - cm2187 2y ago
    Yes I should have rather written “an absence of data”. But still not “unknown”.
- SoftTalker 2y ago
  Best way to think of NULL is "no value." Not "unknown value," as that implies that it is a value you just don't know what it is. Not "missing" value as that even the notion of a value being "missing" tells you something. NULL is no value. It's like a black hole, it consumes anything it touches. Any expression involving a NULL term becomes NULL.

jmyeet 2y ago

NULL is the absence of a value. If you try and treat it as a value, you're going to have a bad time. So an attempted UNIQUE(email_address, deleted_at) constraint is fundamentally flawed. If you treated NULL as a value that could be unique, you're going to break foreign keys. But let's continue the logic of deleted_at being NULL indicating an active account, which seems to the intent here. You end up doing things like: SELECT /* ... */ FROM accounts WHERE email_address = '...' AND deleted_at IS NOT NULL Depending on your database, that may or may not index well. More problematic, you may end up with privacy leaks if someone forgets the last conditional. If anything, you want to reverse this so someone has to go out of their way to explicitly select deleted accounts. There are multiple strategies for this eg using an active_accounts view or table. Lastly, there are lots of potential reasons for an account to be disabled or otherwise not visible/accessible. Takedowns, court orders, site safety, hacked accounts and so on. Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design.

indeed30 2y ago
That's interesting - I believe this is exactly how Sequelize implements soft-deletion.
giraffe_lady 2y ago
You put the "is not null" on the index itself and then simply don't use it for the much rarer queries that are on deleted accounts. Or just use a view for active accounts. Overloading timestamps to carry a boolean on null is awesome as long as you decide that's what you're doing and use one of the several standard techniques to dodge the easily avoided potential downside. This isn't a valid security concern, more than any other incorrect sql query would be anyway. A dev can always write a bad Q, you need another way to address that it's not more likely here because of the null.
chuckadams 2y ago
> Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design. Then don't do that. It's kind of a leap to say soft deletes are categorically bad because someone might confuse "deleted" with "inactive". My users table does the super-advanced thing of having both columns. The ORM also doesn't forget to add the not-null criterion. There's also zero databases in active use where it poses a problem to indexing. Soft deletes suck in their own way, but none of the alternatives are perfect either.

zokier 2y ago

SQL nulls in some ways behave in similar to floating point nans. Of course nans are also weird in their own way, but it is a bit comforting that its not so completely singularly weird.

dunham 2y ago
Also similar to the bottom value in haskell and exceptions in other languages.
giraffe_lady 2y ago
NaN is cool because it's almost like a type that constrains uncertainty. What do we know about this entity? not much! but it's definitely not a number. Calling it anything else would have been a mistake. Null is more confusing because it means different things in different languages. Sometimes it's a more constrained uncertainty, eg this definitely doesn't exist. But in sql it's a less constrained uncertainty, like "undefined" in math. The value of this thing couldn't make sense in this context, but we can't make assertions about its existence.
- mplanchard 2y ago
  There's another comment in here that talks about thinking of NULL as UNKNOWN, and I quite like that. It makes a lot more sense, and I think it would've been a better choice to standardize on. UNDEFINED would also be an improvement.
  - reshlo 2y ago
    UNDEFINED would not be accurate. If your signup form has an optional field for a full name which I don’t fill in, I still have a name. Just because a value is not known by your database doesn’t mean it isn’t defined. E. F. Codd thought about this issue.[0] > Codd indicated in his 1990 book The Relational Model for Database Management, Version 2 that the single Null mandated by the SQL standard was inadequate, and should be replaced by two separate Null-type markers to indicate why data is missing. In Codd's book, these two Null-type markers are referred to as 'A-Values' and 'I-Values', representing 'Missing But Applicable' and 'Missing But Inapplicable', respectively. Codd's recommendation would have required SQL's logic system be expanded to accommodate a four-valued logic system. Because of this additional complexity, the idea of multiple Nulls with different definitions has not gained widespread acceptance in the database practitioners' domain. It remains an active field of research though, with numerous papers still being published. [0] https://en.wikipedia.org/wiki/Null_(SQL) https://en.wikipedia.org/wiki/Null_(SQL)

irrational 2y ago

I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.

svieira 2y ago
Domain-embedded nulls are the bane of my existence.
hyperman1 2y ago
Oh man. I had a talk with a DBA about how oracle could not deal with an adress with no street name - literally a tiny village with 10 houses on 1 nameless town square. It was unsearchable in parts of the app because street='' was interpreted as street is null. DBA kept claiming oracle was right and the town should adapt their naming to our software. This attitude was so prevalent at the time, I sometimes wonder if the rise of noSQL was simply people sick of dealing with Oracle DBAs
- aidenn0 2y ago
  > This attitude was so prevalent at the time, I sometimes wonder if the rise of noSQL was simply people sick of dealing with Oracle DBAs That was definitely one part; another part was sharp corners in MySQL (at least as of 20 years ago; I would be surprised if many of them haven't been rounded off in the meantime). The last part was places with no DBA with developers unaware of how to handle schema migrations.
zo1 2y ago
It's weirder. If you insert an empty string into a VARCHAR field in Oracle, it returns Null back to you when you query that same field. At the very least, I'd expect a software system to behave in a deterministic way. I.e. either throw an error because you're not doing something right (whatever Oracle deems right in this case), or give you back what you gave it, especially for database software who's entire role of existence is to persist data without side-effects.

datadrivenangel 2y ago

SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

grahamlee 2y ago
Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.
- setr 2y ago
  The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. The use of x = NULL instead of x IS NULL is pretty much always a mistake. More importantly, x = value instead of (x = value and x IS NOT NULL) is almost always a mistake, and a stupidly subtle one at that. And for this curse, we get… nothing particularly useful from these semantics. Also the x != NULL case is completely cursed
  - grahamlee 2y ago
    > The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. It's a three-valued logic (though not trinary, which would use a base-3 number system) in a three-valued algebra: specifically, the relational algebra. The outcome of a logical test has three values: true, false, or NULL; this is distinct from Boole's algebra where outcomes have a continuous value between 0 and 1 inclusive.
- tzs 2y ago
  That's not the only time it is weird. There's even a whole book by one of the pioneers of the relational DB model, Date's "Database Technology: Nulls Considered Harmful" [1], covering many of the ways it is weird. [1] https://www.amazon.com/Database-Technology-Nulls-Considered-Harmful/dp/1634624769 https://www.amazon.com/Database-Technology-Nulls-Considered-...

ungut 2y ago

The NULLs in unique constraints quirk actually works differently in ORACLE databases, which is infuriating to say the least. Apparently this comes from some ambiguity in some sql standard, anyone know more about this?

MathMonkeyMan 2y ago
All I know is from this SQLite article: <https://www.sqlite.org/nulls.html https://www.sqlite.org/nulls.html>

al2o3cr 2y ago

FWIW, you can explicitly change this behavior in Postgres as of version 15 - include "NULLS NOT DISTINCT" when creating the unique index.

hiAndrewQuinn 2y ago

SQL NULLs aren't weird, they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNKNOWN = UNKNOWN, because it could darn near be anything: TRUE XOR TRUE, TRUE XOR FALSE, FALSE XOR FALSE, FALSE XOR TRUE... Internalizing this is where SQL's use of NULL / UNKNOWN really becomes intuitive. 4. (TRUE AND FALSE) XOR (TRUE OR UNKNOWN) = (FALSE) XOR (TRUE) per #1 = TRUE. See, it's consistent, you just need to keep in mind that if you have a lot of known UNKNOWNs they're quite parasitic and your final outcome is likely to be, itself, an UNKNOWN. Just like in real life!

thayne 2y ago
If only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.
- dominicrose 2y ago
  Javascript has both null and undefined and I'm not sure that's a good idea. At least in SQL we only have one of them, but it can mean unknown or it can mean N/A or even false. It's like a joker, what it means depends on how you use it.
  - hun3 2y ago
    Or VBA, which has Empty, Null, and Nothing: https://excelbaby.com/learn/the-difference-between-empty-null-and-nothing-in-vba/ https://excelbaby.com/learn/the-difference-between-empty-nul... (and sometimes Missing)
  - hobs 2y ago
    No, it's not those other things, that's just using the tool incorrectly. A NULL is definitely "we dont know", not false, not N/A, especially not any known value.

bunderbunder 2y ago

> ... and this is even less obvious if you’re used to using ORMs. Which is why I continue to be such an ORM skeptic. I agree that they're convenient. But I do worry that we've now got an entire generation of engineers who regularly interact with relational databases, but have largely been spared the effort of learning how they actually work. As another commenter pointed out, if you've learned basic relational algebra then the way SQL nulls behave seems obvious and logically consistent. The logic is the same as the logic behind the comparison rules for NaN in IEEE floats. It's the behavior of C-style nulls that is, always and forever, a billion-dollar mistake.

thrance 2y ago
My experience with ORMs is that most of the time you end up needing to write some queries by hand, in raw SQL. Usually these are the most complex, that you can't express in your ORM's DSL. My point being, I don't think using an ORM really shields you from having to learn how it works behind the scenes.
- globular-toast 2y ago
  It's not even about having to write SQL by hand. In an ORM like Django that's exceedingly rare. But you still need to understand what's going on underneath. In other words, it's the most leaky abstraction there is. I think the popularity is mostly aesthetic and convenience. Most people into ORMs like Django don't really know about layered architecture and that you can keep all your SQL in one place in the data access layer. They just scatter that stuff everywhere in the codebase.
  - feoren 2y ago
    I don't know Django specifically but I'm always floored by how people talk about ORMs. They're only a leaky abstraction if you believe their point is to shield terrified junior devs of the inner workings of the scary relational database. That's an awful way to use ORMs, and the source of most of the flak they get. To be fair, some are designed that way, or at least strongly push you toward it. Stop thinking of ORMs as trying to hide the details of SQL and you'll stop hating them. Instead think of them as a way to compose SQL dynamically, with the full power of your language. SQL is an awful language to write application logic in, because it has horrible support for abstraction, composition, encapsulation, dependency injection, etc. The ORM gives you a way to produce SQL in an environment that actually supports basic software engineering principles. Scattering ORM logic everywhere in the codebase is the point: putting all your SQL in one data access layer is like putting all your arithmetic in one calculation layer. Why would you ever do that? What's wrong with seeing a plus sign in more than one file? What's wrong with seeing language-encoded relational logic in more than one file? I can guarantee you the popularity is not "aesthetic". And convenience is a real thing that actually does reduce costs. People complain about ORMs, but have you seen the absolute horse-shit-level code that people jam into SQL functions and procedures to do the utterly most basic things? The standard for what ends up in SQL Stored procedures is the most unmaintainable garbage in the entire software engineering ecosystem.

kurtbuilds 2y ago

If you want equality testing with nulls, you want to use `is (not) distinct from` instead of `=` and `<>` / `!=`. `1 is not distinct from NULL` => false `NULL is not distinct from NULL` => true `0 is not distinct from 1` => false

blast 2y ago
Having that is much better than not having it, but man is it verbose and confusing.

Recursing 2y ago

Surprised that this doesn't mention "IS DISTINCT FROM" ( https://modern-sql.com/caniuse/is-distinct-from https://modern-sql.com/caniuse/is-distinct-from ) (Although in rare cases that is even weirder: https://stackoverflow.com/a/58998043 https://stackoverflow.com/a/58998043 )

ziml77 2y ago
I'm glad SQL Server finally got this, but I wish the syntax was nicer. It's a multi-word infix operator that gets tough to read. I've been using Snowflake SQL recently and I like that they just made it a function called EQUAL_NULL

osigurdson 2y ago

Weirder still are floating point numbers in SQL.

tzury 2y ago

For Postgres specific approach, you may refer to https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null https://blog.rustprooflabs.com/2022/07/postgres-15-unique-im... Practically speaking, I go with not null, and always set default value.

exabrial 2y ago

null != null is pretty bizarre at first, until you understand the reason the did it was to try to make sense of null-able indexed columns. Not sure why we couldnt have our cake and eat it, but instead we got IS NOT NULL is not the same as != NULL

otteromkram 2y ago

There's another good, technical write-up on NULL behavior in SQL at modern-sql.com https://modern-sql.com/concept/null https://modern-sql.com/concept/null (Note: I am not affiliated with that bloh/website in any way, shape, or form.)

lolpanda 2y ago

I actually like how NULLs behave in SQL. They mean "I don't know" In the modern programming language we all care about Null safety. But no matter how you model your data, you will always run into the situations when you don't know everything. So I believe NOT NULL is not very practical. NULLs in SQL handle these case very well - when the input is unknown your output is unknown

int_19h 2y ago
Except they don't consistently behave that way. If NULL means "unknown", why do they show up in outer joins, or when you SUM an empty table?
- cglace 2y ago
  The most annoying is having to order by DESC NULLS LAST to get the largest value from an aggregation.
dalton_zk 2y ago
I feel like the same, Null equal null is null is totally right
- afiori 2y ago
  I feel like a select for: - col1 = 1 should not return NULLS - !(col1 = 1) should return NULLS - col1 <> 1 should not return NULLS
kijin 2y ago
Agreed. If SQL didn't have NULL, we'd have other special values meaning "I don't know" or "no data" all over the place. Too many newbies hear that NULL is bad, so they declare all columns as NOT NULL and end up inserting ad hoc values like 0, -1, '', or {} when they inevitably come across cases where they don't have data. Which is even worse than NULL.

gxt 2y ago

This has always made queries unpredictable in many scenarios and it should be a feature to turn nulls off entirely and swap them out with Option<T> instead.

solumunus 2y ago
How would you handle unmatched outer joins?
- masklinn 2y ago
  a left outer join b yields tuples of (A, Option<B>), a full outer join b yields tuples of (Option<A>, Option<B>)
- benzayb 2y ago
  By having a default value (non-null) for each declared type of those columns. Or, the user must define a default value in the query itself. Yes, tedious; but, precise and forces the programmer to really prepare for the "unknown" scenario.

galaxyLogic 2y ago

In Object Oriented Context "null" is useful to indicate that some object doesn't have value for that property. What's interesting is, do we mean that in our data that attribute has no value? Or do we mean the real-world object represented by the data does not have that attribute? Does null mean a) We don't know the value of this attribute for this object, or b) We do know that there is no value for this attribute in the real-world object represented by our data. In JavaScript because there is both null and undefined it is easy to assume that undefined means we don't know the value and null means we do know it has no value. EXAMPLE: The attribute 'spouse'. Some people have a spouse some don't. So what does it mean if the value of the field 'spouse' is null? That we know there is no spouse, or that we don't know who the spouse is if any. In practical terms we can say null means "We don't know" which includes the case that there is no spouse.

andai 2y ago
I remember from my databases course at university that NULL means that the database doesn't contain that data, and empty string means that it is known to be empty.
- niij 2y ago
  What is the type is something other than a string? age: null? married: null?
- feoren 2y ago
  That's your professor's opinion, and probably one that does not come from industry experience. Look in 4 different databases and you'll see 9 different conventions. A common one is to have all strings non-null with a default value of empty string. And not all columns are strings; there is no "obviously empty" integer or boolean.
- zo1 2y ago
  Let's also all be reminded about how Oracle DB doesn't let you insert empty strings, and instead treats them as NULLS even if you gave it an empty string initially. https://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null https://stackoverflow.com/questions/203493/why-does-oracle-9... That was a fun bug to find out, after having dealt with quite a few other DBs over the years. It was one of those "No, but surely" and "This can't be! This is Oracle!" moments. Found it while porting some old code that needed to store an empty string as being distinct from a NULL in that same column.

khana 2y ago

[dead]

branko_d 2y ago

NULLs are weird because they are basically two different types under the same name. The 3-value logic type is useful for representing "missing" foreign keys, but 2-value logic type is arguably more useful when searching/sorting/aggregating. I think we would have been better-off by treating FKs (and maybe outer JOINs) as a special case, and using 2-value logic everywhere else.

hot_gril 2y ago

Weird as they seem at first, SQL null handling ends up being convenient the way it is. Part of this is because left/right join give you nulls.

qwertydog 2y ago

SQL NULL is also coerced to different boolean values depending on context e.g. in a WHERE clause NULL is coerced to false, whereas in a CHECK constraint NULL is coerced to true https://dbfiddle.uk/C5JqMP8O https://dbfiddle.uk/C5JqMP8O

wruza 2y ago

I think (blasphemous hot take ahead) that the standards of implementation of relational models are wrong. NULLs still have their (rare) place, but the foremost issue with query results is that they are tabular rather than hierarchical. The main culprits being (1) outer joins that represent or induce nonsensical operations and (2) lack of non-null “zero” values for types like date. Of course hierarchies can make querying more complex, but mostly in cases where the relational logic goes crazy itself and you had to go tabular anyway. If you think of it, distinct, group by and windowing feel like workarounds in tabular mode but would be natural to hierarchies, because everything is naturally distinct and grouped-by by design and windows are basically subtables in these rows. Bonus points you could fetch “SELECT FROM a, b_rows LEFT JOIN b AS b_rows …” in a single query without duplicating `a`s and nullifying `b`s when N <> 1. And when you aggregate through a column in `b`, there’s no headache what to do with join-produced NULLs (unless `b` columns are nullable by your design, then it’s on you). And when it all arrives to a client, it’s already well-shaped for ui, processing, etc. No more: last_a_id = undefined for (row of rows) { if (row.id != last_a_id) { … last_a_id = row.id } … } I’m pretty sure you recognize this programming idiom immediately. Before you criticize, I’m not talking about hierarchical/OO tables. Only about ways of getting and handling query results. You still can reshape a relation like you want. The difference is that a database engine doesn’t have to put it all onto a (N x M x …) table and instead creates sort of a subset of relations which is efficient in space and natural to walk through. It already does that when walking through indexes, selects are naturally hierarchical. All it has to do is to track relations it went through rather than just dumping rows from a set of cursors that it knows the start and end points of, but loses this knowledge by writing into a plain table.

iefbr14 2y ago

When the null concept was introduced to me in the seventies, the only thing I could say was that it would be causing a lot of unnecessary confusion in the future. If you have missing values in your datarecord then that datarecord belongs in an exception-queue. And now some 45 years later people are still discussing it like we did then..

acuozzo 2y ago
> If you have missing values in your datarecord then that datarecord belongs in an exception-queue. This depends on the context, no? I doubt there exists someone with a contact list on their phone which has every single field for every single contact populated. There needs to be some way to codify that a field in a datarecord is unpopulated. Using the "zero value" for the type of the field (e.g., the empty string) is reasonable, but is this necessarily better than NULL? I reckon an argument can be made that this approach is just as likely to lead to bugs. I'm not necessarily in favor of NULL, for what it's worth, but I can't think of an adequate replacement which doesn't reduce to "NULL in sheep's clothing".
- thfuran 2y ago
  I did last week, before I added the first contact.
jfb 2y ago
Sometimes you want UNKNOWN, sometimes you want MISSING.
- iefbr14 2y ago
  Just give the data item a status field, don't fix it by medling with a designated (non)value. And while you are at it you can add some valid-from and valid-to fields for the item. That's how you do it proper.
DangitBobby 2y ago
Missing values are not always an exception. There's a reason modern languages almost universally include an Option type which may contain a Null and allow you to pass it around as a first class value. Good data representations allow you to express missing values.

at_a_remove 2y ago

I have deep but vague thoughts around the concept. My first intuition is that we have put too many things under NULL and None and such. Partially, we use ... and I'll be very broad here ... "variables" as boxes we look in for answers. Answers to questions, answers to "Hey I put something in there for the time being to reference later." If I went into programming terms rather than just SQL, sometimes we get meta-answers. You haven't made the box yet (declared the variable). You haven't decided how the box is structured (picked a type or a length or something). Okay, you did those but the box is virgin (nothing has been placed in the box yet). That kind of thing. An empty set for "yes, you asked but nothing meets those criteria."

kopirgan 2y ago

Recall this really funny dialogue in one of the Blackadder episodes. The princess eyes are as blue as the stone of Galveston Have you seen the princess eyes? No! Have you seen the blue stone of Galveston? No! So you're comparing something you've never seen with something else you've never seen! That's NULL comparison

xd 2y ago

To me, "Unknown" almost implies the possiblity of a value, whereas I've always thought of NULL as being an absence of a value. edit: an empty string, false, 0 are all values.

getnormality 2y ago

I don't see why this is weird. Unique means no duplicates. Nulls can't be duplicates of each other because they're not equal to each other. If you don't like null semantics, you're free to use sentinel values. You can make all the sentinel values the same, or you can make them all different. Either way, you or someone who has to use your system will be back here tomorrow complaining about how weird it is.

trollbridge 2y ago

And SQL null shares the feature with many other languages that any type can be a NULL (although a column can be set NOT NULL). Much like Java, it is no end of grief that a type that claims to be, say, a “NUMBER” is actually “NUMBER | NULLType”.

drzaiusx11 2y ago

Ah yes, someone discovering the existence of three value logic in SQL and expecting 2VL behavior. Classic. We've all been there, right? Personally I wish more languages were like python or ruby and had chosen None or Nil over Null which alleviates the confusion a bit, as those names better indicates that it's NOT an "unknown" (1 unknown value != 1 other unknown, which intuitively makes sense.) In ruby or python it's more obvious that None and Nil are "nothing" types and therefore equivalence makes sense (nil == nil, None == None are both true)

criloz2 2y ago

It is not supposed that null is the bottom value in the universe of all the values that your program can recognize? Why people need to complicate it?, and yeah in that definition `null == null`, but a `null_pointer != null` because null pointer is at the bottom of all the possible pointer value, and null by itself is not a pointer. The same for (0,null), (false, null) and ("", null). null should only be equal to itself. And lastly undefined != null, because undefined is related with structures indicating that a field was not defined when the structure was created

_zagj 2y ago

The simplest end-run around this is to avoid NULLs entirely, which normalization (even just the first normal form) requires.

ludwik 2y ago

Even if we set three-value logic aside for a moment, this behavior of NULL still makes sense intuitively. The value of NULL in a particular table cell is simply a way to indicate 'no value'. If you want the values in a column to be unique, cases where there are no values shouldn't be considered. This plays out similarly in practice. For example, you may want to allow users to optionally reserve a username, and if they do, those usernames should be unique. It's hard to imagine a use case where by wanting a field to be both optional (nullable) and unique, you mean that the field should be optional for a single record (!) and required for all the rest. Of course, you mean that IF there is a value, THEN it should be unique.

boxed 2y ago

All of this would be avoided if NULL in sql was just called "UNKNOWN". Which is what it is. Terrible name :/ Imo, SQL should add "NOTHING", add "UNKNOWN" as a synonym for "NULL", and deprecate "NULL".

whartung 2y ago

This reminds me back in the day when I was writing a DSL for a project. Since the data we were getting was sourced from an RDBMS, I wanted NULL to be a first class concept in the DSL, with similar traits. Early on, I simply made any expression that involved a NULL result in NULL. Naively this was all well and good, but it failed spectacularly in condition statements. Instead of A = NULL == false, I had A = NULL == NULL. And, as you can imagine, a single NULL in the expression would just pollute the entire thing, and since NULL was considered as FALSE for conditionals, any NULL in an expression made the entire thing, eventually, FALSE. Naturally I went back and made the comparison operators always return booleans. But it was a fun little side effect at the time.

jakubmazanec 2y ago

That's why I like EdgeDB and EdgeQL: no null [1] [1] https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql