Null Values in SQL Queries
mitchum.blog
mitchum.blog
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.
That Oracle behavior annoys me each time, though.
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.
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.
All other relational databases differentiate between empty strings and NULL.
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 3 15:56:30 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create table foo(fook varchar2(10) not null);
Table created.
SQL> insert into foo values ('');
insert into foo values ('')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SPORTSBOOK_DOCK"."FOO"."FOOK")
SQL> create table bar(bark varchar2(10));
Table created.
SQL> insert into bar values ('');
1 row created.
SQL> insert into bar values ('a');
1 row created.
SQL> insert into bar values (null);
1 row created.
SQL> select * from bar;
BARK
----------
a
SQL> select * from bar where bark = '';
no rows selectedYes, because it will convert the empty string into a NULL, and fail to insert that NULL into a non-NULLable column.
Emtpy string vs null is not that useful if you have null and undefined.
In my defense, JavaScript treats a value of `undefined` differently from an unset field / variable (e.g. when testing `object.hasOwnProperty('field')`).
Working with xml or json in more typed language like C# is less fun because there is not distinction in the language instead you end up using dom like checks to see if the key was provided or was set to null.
.Net code generator use to add an extra property called [property name]Specified for xml serialization so you could do null values or not send the node. Definitely prefer more direct language support for no value provided vs a null value.
PS undefined in JavaScript is not that useful IMO. But it could be useful with more strict language.
Did you want to update the field to null or not update it at all is another one.
Re: Did you want to update the field to null or not update it at all is another one.
Who, the user or DBA? And why? Users don't know a Null from a horse; you shouldn't make them think about that, that's an internal guts thing.
On updating its related to prompting typically, again communicating user intent, did the user update a field from having a value to not having a value (they purposely blanked it out) or they didn't touch the field and maybe it wasn't even shown so don't modify it. Basically am I going to generate a update statement with set field = null or no set at all for that field. This is trivial in json to send through application due to null vs undefined.
I'm still not following. What exact hand or keyboard/mouse movements constitute "left it blank"? "Leave" means to "move away from". What is moving away from what?
Usually one stores a time-stamp for input prompts (screens). If the prompt never appears, you then have no time-stamp and/or record for it. Why would a broken prompt create a record to begin with? I'm missing some context you seem to be assuming. You may be assuming behavior of a specific UI product or stack.
I think you get it since you talk about time stamps. Just as you could record all mouse movements and keyboards to get higher fidelity you could break a single record into multiple with times stamps to record each step in a form, then the lack of record would be distinct from a record with all nulls along with time stamps. You could also do a record per field (which I have seen more than once) with metadata per field as other columns.
But without all that a system that supports null and some empty value gives you more fidelity than just null that again in my experience is practical.
Empty string and null work fine with strings for this purpose but for other datatypes you start needing to pick special values like the minimum value for a 32 bit int or 0 date etc.
Maybe you encountered a specific product's edge-case where it helps, but I'd rather trade away 9 other headaches to get your edge case headache. It may be the case that Product X doesn't allow time-stamps, but should we gum up all strings to make just Product X happy? I don't see how the tradeoff math favors that. Plus, it encourages bad designs by plugging problems with funny non-printable codes. Let's not enable slop-heads.
Re: but for other datatypes
For the purpose of this discussion I'm limiting it to strings, where the majority of null-related headaches are found in my experience.
In at least one project I worked on, there was a lot of survey data entered from paper surveys used in the 90s and early 2000s. The structure of the survey included pass-through questions: "If you have ever smoked a joint, please check yes and answer questions 42 and 43. If you have not ever smoked a joint, please check no and TURN THE PAGE."
One can certainly build a logic to process these replies (check answer to question 41, tally blanks in questions 42, 43 according to answer to question 41) but since these questions and answers were also entered into the computer in the olden days, NULL was used if questions 42, 43 were passed through, while blank was used if questions 42, 43 were left blank (and 41 was yes).
How times have changed.
Want to know if a Boolean field is unset? Well it’s no longer Boolean because you now have 3 states for that field. So why not use a char, U/Y/N with the default being U?
NULL might have made more sense 30+ years ago when systems were more resource constrained but it doesn’t make sense now for most of the problems people are trying to solve day to day. If anything, it creates more problems.
Just to be clear, I’m not saying they should be removed entirely but rather that they shouldn’t have to be explicitly disabled on every CREATE.
I will say one use case for NULL that is hugely helpful is outer joins and nested queries. However these don’t generate high performance queries so if you’re having to rely on them then you might need to rethink your database schema anyway.
So essentially I don’t disagree with you, I just think you’re being too nice limiting your complaint to string fields.
Well because instead of using a type that exactly encodes the concept of "yes/no/unset" (nullable boolean), you'd be using a type that encodes "any 1-character text, with arbitrary meaning and most of the values being nonsensical"
Capturing those edge cases is non-trivial compared checking the value of a char.
In an idea world your unit tests and CI pipelines would catch all of those but that depends on well written tests. Thus in my experience having fewer hidden traps from the outset is automatically a better design than one that perfectly fits an academic theory but is harder to support in practice.
One way of implementing the UI for this is to have a checkbox labelled "Inherit" or "Use default", and if it's checked, disable the actual textbox (and put NULL in the field).
I've also run into similar patterns with data telemetry. I want to accurately represent the data I received, and so if some older version doesn't include a particular value, I want to store that as NULL, because empty string is a potentially valid value. If you "normalize" NULLs to empty string, and then it makes it impossible to answer a question like "What % of systems have a value for this data item?" since it includes versions that can't get it at all.
Take the middle name example: "What was U.S President Theodore Roosevelt’s middle name?" when you know he didn't have a middle name.
Are you suggesting that a blank is the correct choice here?
I don't think it's accurate to say they have a blank middle name. I think it's better to say they don't have a middle name.
Both are entirely valid methods of encoding the data, and require knowing how the data is intended to be collected, entered and used to determine the best choice.
You're assertion is that it's counter-intuitive to use the database's ability to encode a lack of information to denote a lack of knowledge about the information, and should only be used to denote the information does not universally exist? And additionally that this is so well accepted that to do otherwise would not be accepted by any peers?
If so, that's a fairly bold assertion to make when you haven't provided any any evidence or reasoning to back it up.
If the data needs to differentiate between those cases and a separate boolean bit of data to track whether it was collected is no feasible (as it so often isn't in the real world), then you do what you must. How that works out in the real world would be the policy for how to interpret the data in that field. It's really no different than any time you've seem a field that is named something counter-intuitive to what it holds in some schema. Something tracks that difference, whether it be institutional knowledge, come conversion code, or a label prior to display. That's what I mean by it being a "schema/data policy question".
But SQL and the relational model is meant to be a logical system, and I'm interested in preserving some conceptual integrity in the domain modelling.
To keep with the example of asking questions, if a sruvey is asked and each question is represented by field in a table for that survey, but they also want to track whether any particular question is answered, there are multiple ways to track this information. It can be done through a separate table (or multiple), it can be done with an extra boolean field per original question field, or it could be done by making each question field nullable, and ensuring that no question answered will ever be left null.
Which of those cases breaks the conceptual integrity of the domain modeling? I would argue that as long as they are applied consistently and accurately, none of them break it.
If all your access is mediated by a layer that takes care of this, then complaining about how it's stored in SQL is no different than complaining about how your DBMS is storing the data on disk. It doesn't matter, you aren't supposed to be doing anything with it there anyway.
Data integrity is not something a database can ensure, for any non-trivial use. ANSI SQL provides tools to help ensure data integrity, through data types, referential integrity checks if you define them and they are supported, but ultimately, those tools can only account for what the database supports. Will they prevent the favorite_color field from receiving the data that should have been entered into the pet_name field? Unlikely?
It's no different in programming languages and bugs (as a data integrity problem is a bug in data domain). Using C, Java, Rust and Haskell will not prevent all your bugs, and Perl, Python, JavaScript and Bash do not mean you will have bugs. A bug free Perl script is possible, and so is a bug riddled Rust script, as not all bugs are (or even can be) caught by the assurances the more strict languages provide.
Unless all your SQL in a schema use is through a directly connected SQL client that loads data directly from native SQL formats, runs queries, and exports data as the output of those queries, the applications that have ingress and egress to that database are what really matters for data integrity, and worrying that a date field might contain 1111-11-11 as a special sentinel value that means something other than what null value in the same field means is being unable to see the forest for the trees.
- I know that Theodore Roosevelt has no middle name.
- I don't know what Theodore Roosevelt's middle name is
The semantics of each case are different in a subtle but important way. Consider the following two predicates
'Jim' = [no value] vs 'Jim' = [I don't know]
The former predicate is obviously false. 'Jim' is not equivalent to a non-existent value. The latter predicate however, can't be assigned a truth value. It's entirely possible that the unknown value could later turn out to be 'Jim'.
The key issue is that SQL treats NULL values according to the latter ('I don't know') rule and ternary (Kleene) logic, as TFA discusses. That, in turn means that using NULL to represent [no value] will lead to painful and hard to diagnose logic bugs (e.g., WHERE x = 1 OR x != 1 not returning all records).
[edit: clarifying why bugs will arise]
In a sense, empty string is available as a marker, just as NULL is available as a marker, to satisfy either scenario. I think the possibility of three-valued logic applies in either scenario.
I'm not at all convinced empty (non-null) string is appropriate for many real world applications, just as empty (non-null) date is appropriate for similar scenarios.
Eg. Date of death: unknown - NULL; not dead yet - NULL
In the database you need to represent if the user entered a blank name “”, or if the middle name was never even requested NULL.
Only in Oracle is it not possible to make this distinction. Oracle is definitely broken in this regard.
Inserting a string in the middle name column “unknown” or “unspecified” is a clunky workaround.
The problem with Null is that it can be either a value or show the lack of presence, and in JS because both undefined and null are values you can't depend on them being used for their semantics either (you can try to enforce their use but it that's no guarantee).
SQL suffers this exact same problem. I wonder what SQL would look like without Null.
Select id, Option (key)
From table
Insert... Some(5), None::Int
Interesting
If you really wanted to, in Haskell you could do something like:
data AdvancedMaybe a = Just a | Nothing | PassiveNothingI think Allowing "empty" values, and not allowing Null would be better.
If SQL had Option (Maybe) branch matching it would be an interesting replacement, and then coalesce would not be needed.
SELECT id, MATCH k WITH (
SOME(k) THEN k,
NONE THEN default,
END) AS something
FROM ...
Just spitballing for fun on what it might look like.
As a base table value, it's easy to mechanically avoid NULL in SQL. Every nullable column is, in the most general case, simply a column that doesn't belong in the table but in a different table whose primary key is alsona foreign key referencing the original table’s primary key.
No, the trouble with null is that it's a supertype that breaks the type systems -- if you actually wanted to have safe code in the sense you expect typesafety to give you, you should be checking for NULLs everywhere. Because a codebase can be changed from never-null to sometimes-null, and the type system is happy to let it go through, because never-null was not actually a thing -- we just imagined it for our convenience.
The problem with null is that, in general, if you didn't implement an explicit handling for it, then it's presence will do the wrong thing.
The primary benefit of rust's Option is that you can safely not check for null.
SQL is in fact the same problem -- we just don't bother to handle the null until it bites us in the ass. For example, booleans don't actually exist, because really a bool has values (True, False, Null). Which breaks your Boolean logic because you're using trinary values (with unintuitive results, because there isn't really a good answer). Which leads you to innocent queries including both false positives and false negatives, silently, when nulls are present in your data [0]
SQL does however allow for the column constraint NOT NULL, but there's nothing that actually enforces your queries to handle the possibility of nulls correctly (because, by default, everything is nullable, and your queries would be horrificly verbose) so queries tend not to handle the case correctly until they run into the problem.
The problem people tend to not understand is that we should be checking for nulls everywhere, and we only don't do so because it would be insanity. The primary benefit of moving nulls into an Enum is that we can now safely specify that thing is NOT NULL (and our compiler will enforce it). And as it turns out, most of your data does not need, or want, to be nullable
[0] https://www.google.com/url?q=https://sigmodrecord.org/public...
It's also true for AWS' DynamoDB offering.
2. NULLs are used by SQL functions and operators as an "unknown" value. So, for example, "NULL AND TRUE" is NULL, because we could substitute NULL with TRUE or with FALSE to get different results, but "NULL AND FALSE" is FALSE, because no matter what we substitute NULL with, the result will always be FALSE.
3. Clearly all these valid uses of NULL do not indicate "flaws" in the database design.
4. Database normalization isn't always a good thing, and beyond a certain level it's almost always a bad thing, so using normalization methods as a standard for whether something is "flawed" is probablyn ot the best idea.
5. No database normalization method, as far as I know, actually tries to eliminate NULLs, so I don't know what "(See database normalizations)" refers to. Can you clarify?
Talking strings, usually if you don't want a null string, you also don't want blanks (white space) either. I'd like to see auto-trim in the standard, and also a minimum length specifier. We then wouldn't need to deal with nulls. A single min-length-when-trimmed-and-denulled integer value would replace a lot of repetitious hubbub in typical CRUD apps. D.R.Y. it! You'd have one attribute "cell" that would replace the equivalent of:
IF length(trim(denull(inputValue,'')))
< this_fields_min_length THEN
raise_data_too_short_error(...);
Further suppose that you have an external function in your codebase to estimate how many paychecks you've paid to someone, but the author doesn't know about any "0000-01-01" conventions your office uses. Without that, you'd see that Joe New Guy has worked here about 2,020 years, so we've probably issued him about 48,000 checks. If only you'd used null, then that function would have calculated "today() - null", which in any sane language would raise a type exception and alert you to the problem.
Nulls are beautiful. They have meaning. Lots of people misuse them, but that doesn't mean they're not valid and useful.
That question doesn't have an answer because there is no elephant on my desk. It can't be represented by any color, the answer needs to indicate that there is no value.
(Specifically, not returning any rows when queried, which is different from null)
It is hard to model without requirements but you probably need a table for type of desks, another for desk instances with location and another one to many linking table for items/colors.
Why? I like two lamps and no elephants. By normalizing, this new requirement is simple to represent compared to jamming more fields into the desk table.
Or if another person prefers penguins you don’t end up with penguin_color columns.
It is possible the one table design works, but the introduction of nulls is a smell that indicates maybe the design is a problem.
There's really no justification to avoid nulls as a principle.
Arguably, you might consider that it is a bad design. Perhaps there should be a paycheck table that you can sort by date, instead of a field in the employee table that is updated.
(Of course this is hypothetical, so it is hard to argue use cases)
But a paycheck table does help with the second example because now you don’t need to estimate number of paychecks either. Which will help with unpaid leave or sabbatical situations to give you an accurate number.
But this is what they are getting at with bad design. (Potentially) As far as normalization goes.
I certainly won't argue that people come up with some bad designs! Heaven knows I've dealt with some of them. I just wanted to point out that there are situations where null is perfectly reasonable, even though there are plenty where it surely isn't.
According to my old SQL Server text book (written by the SQL Server team) NULL should be avoided because it as so many different interpretations (I think forty was mentioned).
Null should equal null like in every other programming language even SQL group by do null equals null which is even more inconsistent.
CREATE INDEX idx ON tbl (coalesce(col, ''))
NULL = Undefined or No Data. Whereas a blank field can, in and of itself, be data. It may indicated something is intentionally left blank.
But for those times where you want to consider them the same, it would be nice to have a setting.
(Note that I admit the possibility that this may exist already, like most my great ideas.)
(Microsoft's SQL Server still defaults to the non-ANSI NULL behavior where a = b when both are NULL, and that's something that still pings on checklists of SQL Server if it follows ANSI standards. SQL Server is kind enough to let you enable/disable the behavior, and likely that would persist even after the default switches to meet the standard as the docs assure will happen "in some future version".)
Think of SQL's NULL like it's a NaN.
One interesting problem that arises when you use nulls is that it can be difficult to ensure people actually use them when it's appropriate to do so. Case in point I have a field that is essentially an optional positive integer, so obviously I made it a nullable unsigned int. A few years later there's a pretty even spread of nulls and 0s in there to indicate the same thing -- to a lot of consumers, they behave the same because their business logic basically says "if (foo->field) do stuff" which works either way. In the end I changed it to a non-nullable field using 0 as the null stand-in, which is semantically worse, but ended up making interesting searches over this data set a lot easier.
On the one hand, perhaps the more correct answer would have been to yell at people putting 0s in when they should have been putting nulls in. On the other hand, we put constraints on fields for a reason...
I'm writing in Go, so my structs all have empty values unless specifically initialised. This does sometimes mean that I get null uuid's (0000-000000-00000-0000) inserted into tables, which Postgres doesn't understand as null and cheerfully returns as a valid uuid. This has been my only real pain with using nulls.
I've contemplated modding the database driver to interpret nil-value uuid's as null, but that seems a little drastic. Anyone got any better ideas?
`database/sql` does have an interface which lets you define your own marshaling code though. Using it is very simple and would let you marshal an all-zero UUID into NULL easily enough.
1. NULLIF(the_uuid, '0000-000000-00000-0000')
https://www.postgresql.org/docs/current/functions-conditiona...
2. on insert/update rules to rewrite the uuid (probably using NULLIF)
SELECT * FROM my_table WHERE my_column != 5
... WHERE my_column IS DISTINCT FROM 5 SELECT * FROM my_table WHERE my_column NOT IN (5, 6)SELECT * FROM my_table WHERE my_column NOT IN (5, 6) AND my_column IS NOT NULL
happy now?
SELECT * FROM my_table WHERE my_column NOT IN (5, 6)
SELECT * FROM my_table WHERE my_column NOT IN (5, 6) AND my_column IS NOT NULL
select mumu from kaka where mumu not in (5, 6) or mumu is null.
That will return everything, null included, except for 5 and 6.
But wait, there is more. If, for example, performance is the main issue here above query is quite slow. Even on an indexed table on column mumu, it will still do a full scan of the table before returning. How to improve performance in this case? Well, you use LEFT join on itself. Implementation is left as exercise for reader :D.
QUERY PLAN
---------------------------------------------------------------------
Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8)
Index Cond: (b = 333)
QUERY PLAN
---------------------------------------------------------------------
Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8)
Index Cond: (b IS NULL)
(I would heavily suspect that both MSSQL and MySQL have similar behavior here; this is an easy optimization for a query planner.)
And it has done so for many versions, 8 is just the default redirect I got. The very first sentence on that page is:
>MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value.
It works effectively everywhere, with the restriction that it can only do one null-optimized comparison per index (e.g. `(a == 1 or a is null) AND (b == 1 or b is null)` will only optimize one piece). Which is a potentially-significant restriction that makes me curious about the internals... but it does exist, and typical for MySQL is clearly documented.
`select * from table where column not in (5,6)`
Can you name one that does?
COALESCE(my_column, -1)
[1]: https://www.postgresql.org/docs/current/functions-conditiona...
SELECT * FROM my_table WHERE my_column != 5 OR my_column IS NULL;
FWIW I agree with you that NULL's are counter-intuitive. While I am more or less aware of all the various ways to account for them, I still gravitate towards SQL schemas without NULL's since I prefer the intuitiveness of 2VL when writing or reading SQL.
Also a null is no equal to anything.. not even another null
This will print false in SQL Server
if null = null print 'true' else print 'false'
Wrong. It is equal to UNKNOWN:
https://docs.microsoft.com/en-us/sql/t-sql/queries/is-null-t...
if null = null print 'true' else print 'false'
https://docs.microsoft.com/en-us/sql/t-sql/language-elements...
If I'm reading it right, (null = null) is unknown, which is falsy (except with ansi_nulls off, then it'll be true). (null is null) is true.
I don't think you can test null = (null = null), i.e. null = unknown. Let me know if that's possible somehow, I can't get it working.
shortcuts "OR is null", works within functions.
A NULL value technically means it's unknown. An unknown value might be 5, hence why it's not in the result set. Some abuse NULL to mean "value doesn't exist". But a value that doesn't exist can't be 3, or 42, or any other value that's different from 5, so in that regard shouldn't be part of the result set either.
Others again abuse NULL to mean "doesn't apply". And in that case I think it makes sense to include the row in the result set. For example, if I write a query to get all people who's middle name is not "William", I'd most likely want people without middle names included.
Maybe we should have introduced NEX (non-existing) and NAP (non-applicable) as possible values in addition to NULL?
No, that would muddy things in my opinion, like it did to JavaScript. Instead, have more operations/functions for dealing with them in a more "normal" way, so that we can say "WHERE x <> 5" and get results one expects. I'm not sure the syntax, and my drafts would take a lot of time to explain. To give a taste, maybe have something like "WHERE ~x <> 5" in which the tilde converts x's value to the type's default, such as a blank in the case of strings.
If the different reasons for "emptiness" matter, then usually it suggests the need for a "status" column of some kind so that queries can be done on the reasons. I'd need to study domain specifics to recommend something specific.
Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction.
In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator.
In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.
I'd almost always use it no matter what. In fact if starting SQL over via a DeLorean, I'd reverse it to require "~" to make it null-sensitive.
It's analogous to case-sensitive comparing. The vast majority of the time you don't want case-sensitive comparisons such that case-insensitive should be the DEFAULT, and you only add extra specifiers/functions if and when you need to do case-based comparing.
If not, then you either end up with SQL full of "toUpperCase(x)", or problems keep popping up if somebody typed stuff wrong or the UI coders messed up.
Similarly, if your SQL is full of NVL() or DENULL() or whatnot, it means the language was poorly factored for your domain, or even most domains. It flunked D.R.Y. in design, such as comparisons having the wrong default behavior.
Codd (the inventor of relational algebra) actually suggested this. I think the primary source is a book that may not be on the web. There's some discussion here (mostly saying why they think it didn't happen and wouldn't work out): https://arxiv.org/html/1606.00740v1/
If a value is unknown, you don't know if it is different from 5, so it would be incorrect to return in the query.
“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.
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.
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.
> 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.
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.
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.
(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 NULLThese 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.
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.
SELECT ... FROM ... WHERE blah NOT IN (SELECT foo FROM bar);