# Why does SQL assume you don't want to indlude NULLs in queries?
**URL:** https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959
**Category:** Factual Questions
**Created:** [May 27, 2010, 3:33pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959 "2010-05-27T15:33:15Z")
**Posts on this page:** 20
**Page:** 2
### Author:  [@Derleth](https://boards.straightdope.com/u/Derleth)
#### Post date: [May 27, 2010, 10:39pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/21 "2010-05-27T22:39:38Z")
OK, now I’m curious. Taking Date as paradigmatic, _how_, in the general case, do you avoid NULLs in SQL? Take this example:
You’re trying to build a database that contains the names, hair colors, and ages of everyone in your family. Now, your uncle is bald and, as your maiden aunts are all ladies, well, a lady never tells her age and a gentleman never asks.
I guess you could make ‘bald’ a hair color by doing gross violence to the concepts involved, but how do you handle the ages of your maiden aunts?
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [May 27, 2010, 11:08pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/22 "2010-05-27T23:08:56Z")
> [@M.Constant](#):
>
> To be a bit pedantic myself:
> “select \* from clients where (state not in ('NY,‘CA’) or state is null)”
> Won’t give you any different results than your first query.
> It would just give you anything where state is in (‘NY’,‘CA’) or where state is null.
>
> You need something more like:
> select \* from clients where state not in ('NY,‘CA’) AND state is not null
Sorry I don’t think that’s right. I’m at home now wo I don’t have the query in front of me, but when I made the OP I was quoting an actual query (the important bits of a larger query anyway)
this query…
select \* from clients where state not in (‘NY’,‘CA’)
returned less results than this query…
select \* from clients where (state not in (‘NY’,‘CA’) or state is null)
also… the whole point of my OP was that I _wanted_ to have the records with nulls returned. your query would not achieve that.
---
### Author:  [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)
#### Post date: [May 27, 2010, 11:09pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/23 "2010-05-27T23:09:45Z")
In that case, you would build a table with identifiers for the name, and then link each name to a table containing hair colors, and a separate table containign ages. For those which are known, you will have a relationship, but for those which are unknown, there will be no relationship. It’s the ultimate in normalized structure. 🙂
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [May 27, 2010, 11:14pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/24 "2010-05-27T23:14:44Z")
> [@Derleth](#):
>
> OK, now I’m curious. Taking Date as paradigmatic, _how_, in the general case, do you avoid NULLs in SQL? Take this example:
>
> You’re trying to build a database that contains the names, hair colors, and ages of everyone in your family. Now, your uncle is bald and, as your maiden aunts are all ladies, well, a lady never tells her age and a gentleman never asks.
>
> I guess you could make ‘bald’ a hair color by doing gross violence to the concepts involved, but how do you handle the ages of your maiden aunts?
Not that I am trying to pick a side or add an answer here, but in our system we have a state code of ‘NA’ which stands for ‘Not Applicable’.
Not saying that’s right or wrong, just providing an anecdote 🙂
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [May 27, 2010, 11:15pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/25 "2010-05-27T23:15:44Z")
> [@crazyjoe](#):
>
> In that case, you would build a table with identifiers for the name, and then link each name to a table containing hair colors, and a separate table containign ages. For those which are known, you will have a relationship, but for those which are unknown, there will be no relationship. It’s the ultimate in normalized structure. 🙂
So what will be contained in the foregin key ‘hair colour’ field? NULL?
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [May 27, 2010, 11:41pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/26 "2010-05-27T23:41:31Z")
> [@RaftPeople](#):
>
> I personally think the negatives (increased stmt complexity and potentially more difficult to optimize) outweigh the positives (perfect accuracy and clarity, but this can be achieved with simpler methods many times).
I’m curious, then. How would you do an optional foreign key, where a record in A may or may not have a parent record in B, without NULLs?
I’ve seen cases where people put a special record in B whose sole purpose is as a placeholder pkey for records in other tables that don’t reference any B’s, and therefore would otherwise have a NULL fkey.
OK, so you’ve eliminated pesky NULLs. Now, all you have to do is remember to filter out that magic record every time you do a query on table B! How has this made anything simpler? And why is perverting the purpose of a record to make a hardcoded special value for an fkey somehow better than simply _not putting an fkey value_ where there’s no foreign record to reference?
Also, left joins. What placeholder value do you specify for records in the right-side table that don’t match the left side, if there are no NULLs? You can’t use 0, because 0 may be a valid value in an integer column. You can’t use empty-string, because that may be a valid value in a string column. If only we had some special marker to indicate that no data exists!
OK, rant over.
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [May 28, 2010, 2:09am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/27 "2010-05-28T02:09:55Z")
> [@friedo](#):
>
> I’m curious, then. How would you do an optional foreign key, where a record in A may or may not have a parent record in B, without NULLs?
Unless I’m misunderstanding your question, one way you can accomplish this is by setting up a third table (table C) that contains records representing the relationship between the two tables (i.e., like what is done when creating a many-to-many relationship).
For example, this table might contain only two columns: one that references the record in A, and one that references the record in B. This wouldn’t interfere with your ability to use LEFT JOINs either, since the absence of a record in table C can be thought of as an implied NULL.
ETA: I’m of the mind that NULLs can indeed be useful in a database, but that their usage should always be individually justified.
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [May 28, 2010, 2:29am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/28 "2010-05-28T02:29:58Z")
> [@LilShieste](#):
>
> Unless I’m misunderstanding your question, one way you can accomplish this is by setting up a third table (table C) that contains records representing the relationship between the two tables (i.e., like what is done when creating a many-to-many relationship).
So now you’ve added a whole new join table just to get rid of NULLs. In addition to making what _should_ be a simple N:1 join more complicated, you’ve now lost transactional enforcement of the N:1 relationship, because the presence of a join table means you could accidentally make it N:N. Normalization ruined and complexity added, all for the noble cause of NULLlessness. ☹
> [@](#):
>
> ETA: I’m of the mind that NULLs can indeed be useful in a database, but that their usage should always be individually justified.
You have my complete agreement there.
---
### Author:  [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)
#### Post date: [May 28, 2010, 2:35am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/29 "2010-05-28T02:35:23Z")
> [@friedo](#):
>
> I’m curious, then. How would you do an optional foreign key, where a record in A may or may not have a parent record in B, without NULLs?
Don’t know the details of the database, but a quick answer is FKEY=-1. This eliminates a NULL from FKEY but maybe you were really asking about how to eliminate the nulls on left join? If that is really the question, I wouldn’t propose that, when you join you do need to know if you have a matching row or not.
> [@](#):
>
> I’ve seen cases where people put a special record in B whose sole purpose is as a placeholder pkey for records in other tables that don’t reference any B’s, and therefore would otherwise have a NULL fkey.
Adding a magic record would probably be the last resort to solve just about any problem in the DB, although I probably have done it once or twice.
> [@](#):
>
> OK, so you’ve eliminated pesky NULLs. Now, all you have to do is remember to filter out that magic record every time you do a query on table B! How has this made anything simpler? And why is perverting the purpose of a record to make a hardcoded special value for an fkey somehow better than simply _not putting an fkey value_ where there’s no foreign record to reference?
>
> Also, left joins. What placeholder value do you specify for records in the right-side table that don’t match the left side, if there are no NULLs? You can’t use 0, because 0 may be a valid value in an integer column. You can’t use empty-string, because that may be a valid value in a string column. If only we had some special marker to indicate that no data exists!
>
> OK, rant over.
As I said, you need to know if the join found a matching row in the other table, need nulls in that condition.
But in some (not all) cases of a field in a table, it isn’t important to make the distinction between blank and “could be blank but might not be blank, not really sure, dang I wish we knew for certain” because at that point all you know is that you don’t have the information. If the information is not required (as in not “absolutely required for the proper functioning of the app”), then **Lobsang’s** query should be able to return the “desired” result without having to worry about how nulls are handled in conditionals. In other words, if the value is not NY or CA (for any reason, we don’t care why, which is quite often the “desired” query) then include it in the set.
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [May 28, 2010, 4:26am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/30 "2010-05-28T04:26:34Z")
> [@friedo](#):
>
> So now you’ve added a whole new join table just to get rid of NULLs. In addition to making what _should_ be a simple N:1 join more complicated, you’ve now lost transactional enforcement of the N:1 relationship, because the presence of a join table means you could accidentally make it N:N. Normalization ruined and complexity added, all for the noble cause of NULLlessness. ☹
Well, that was just an example of how a foreign key relationship could be implemented/enforced without the use of a NULLable column. Your point is valid: this type of implementation is not optimal in _all_ situations. (However, it is still possible to ensure the table does not inadvertently allow many-to-many relationships from being created.)
IMO, an abundance of NULLable columns is kind of like database de-normalization; its validity will vary from database to database - but should be avoided when possible.
---
### Author:  [@TriPolar](https://boards.straightdope.com/u/TriPolar)
#### Post date: [May 28, 2010, 4:38am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/31 "2010-05-28T04:38:06Z")
> [@Rysto](#):
>
> Please tell me that no other database vendor has copied Oracle’s idiocy on NULLs and zero-length strings.
My knowledge of Oracle is limited. I have heard it said that in Oracle, SQL NULL is equivalent to a 0 length string (NULL STRING). I know of an associative database that predates SQL and Oracle. It doesn’t have an internal NULL data type (thats what SQL NULL is), so it uses special strings to represent SQL NULL. That occasionally causes a problem. But it can also maintain non-relational data structures that are extremely difficult to implement and inefficient in relational systems. It is also much more efficient at many SQL operations, and far more scalable than relational systems. So choose your poison. Everyone considers a system that doesn’t work the way they want it to, to be idiotic. But using a product that doesn’t do what you want it to is idiotic, as well as expecting a product to do what you want it to when it doesn’t is idiotic.
---
### Author:  [@TriPolar](https://boards.straightdope.com/u/TriPolar)
#### Post date: [May 28, 2010, 5:03am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/32 "2010-05-28T05:03:51Z")
> [@Derleth](#):
>
> I guess you could make ‘bald’ a hair color by doing gross violence to the concepts involved, but how do you handle the ages of your maiden aunts?
What gross violence? What concept? ‘Hair Color’ is the name of a column. If it is defined to contain values ‘Blond’, ‘Brown’, ‘Red’, ‘Black’, ‘White’, ‘Gray’ and ‘Bald’, what would the problem be? You can add values like ‘Not Known’ and ‘Only Her Hairdresser Knows For Sure’. If you think ‘Bald’ is not a color, then why would ‘Black’ be a color? ‘Black’ is the absence of color.
---
### Author:  [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)
#### Post date: [May 28, 2010, 1:59pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/33 "2010-05-28T13:59:36Z")
All of these arguments illustrate the often dismissed necessity of some formal education in data management. Sure, to a lay person, it looks simple, you just exclude the states you don’t want, and you should get all the other records, right? Except because of some necessary constructs such as NULLs, it isn’t quite that simple.
In other words, if Lobsang’s boss gets pissed because he discovers his reporting has been somewhat inaccurate, he (the boss) really has no one to blame but himself.
---
### Author:  [@DanBlather](https://boards.straightdope.com/u/DanBlather)
#### Post date: [May 28, 2010, 5:28pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/34 "2010-05-28T17:28:19Z")
> [@arseNal](#):
>
> I think we’re better off with our computers being “pedantic.” To me that just means they’re adhering to spec and not leaving anything up to the (dbms) developer’s personal interpretation, despite what some people might think of as making more sense.
If that is not the behavior you want, you should have set up your table so that it would not allow NULL in that field. You could have used an empty string.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [May 28, 2010, 5:42pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/35 "2010-05-28T17:42:09Z")
FYI most of my queries in the past are likely to be fine/unaffected as they do not work on fields containing nulls.
Also, I was _kind of_ aware of this behavour of nulls for a while. It did turn up in one of my earlier queries where I ended up adding the ‘or [field] is null’ bit.
What prompted me to post _this_ thread was that it reared its ugly head in a recent event where I had simply forgot about it (and also happened to not be aware that one of the fields I was working on contained nulls)
You can’t really blame my boss for my lack of formal training. My SQL has developed over the years, and the use of it is not a primary job requirement. I’ve just been able to do useful things for the company over the years with an ever expanding knowledge/ability to use sql. For the most part any disadvantage of not having had formal training has had little or no impact on the ‘service’ I provide as the company’s sql guy.
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [May 28, 2010, 6:07pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/36 "2010-05-28T18:07:14Z")
> [@friedo](#):
>
> So now you’ve added a whole new join table just to get rid of NULLs. In addition to making what _should_ be a simple N:1 join more complicated, you’ve now lost transactional enforcement of the N:1 relationship, because the presence of a join table means you could accidentally make it N:N.
Maybe I misunderstand the whole discussion, but if I want to avoid NULL values for the hair\_colour field, I would set up my tables like this:
Table Person
columns:
person\_id Primary Key
person\_name
Table Hair\_colour
columns:
person\_id Primary Key and also Foreign Key referencing Person
hair\_colour
There can only be one row in Hair\_colour for each Person. People that are bald will have 0 rows in table Hair\_colour.
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [May 28, 2010, 6:26pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/37 "2010-05-28T18:26:20Z")
> [@M.Constant](#):
>
> To be a bit pedantic myself:
> “select \* from clients where (state not in ('NY,‘CA’) or state is null)”
> Won’t give you any different results than your first query.
> It would just give you anything where state is in (‘NY’,‘CA’) or where state is null.
>
> You need something more like:
> select \* from clients where state not in ('NY,‘CA’) AND state is not null
> [@Lobsang](#):
>
> Sorry I don’t think that’s right. I’m at home now wo I don’t have the query in front of me, but when I made the OP I was quoting an actual query (the important bits of a larger query anyway)
>
> this query…
>
> select \* from clients where state not in (‘NY’,‘CA’)
>
> returned less results than this query…
>
> select \* from clients where (state not in (‘NY’,‘CA’) or state is null)
Confirming what Lobsang said:
If you want to return rows with a null value for state, then you would need to say (state not in (‘NY’, ‘CA’) or state is null)
If you want to eliminate nulls, then you would need to say
state not in (‘NY’, ‘CA’) and state is not null
which would be the same as
state not in (‘NY’, ‘CA’)
(see proof below)
The SQL behaviour makes sense to me, if you consider NULL to mean ‘unknown value’. For the customers that have NULL, I don’t know if they are in NY or CA - they might be! So when I ask “show me customers for which I _know_ that they are not in NY or CA”, I should not show the NULL customers - those _could_ be in NY or CA. If you ask “show me customers for which I _know_ that they are not in NY or CA, and _also_ show me the customers for which I do not know the location” then it’s obvious you have to include the “and state is null” condition.
Example (using Oracle)
```auto
SQL> create table state (state_id integer, state_code char (2)) ;
Table created.
SQL> insert into state (state_id, state_code)
2 select 1, 'NY' from dual
3 union all
4 select 2, 'CA' from dual
5 union all
6 select 3, 'AK' from dual
7 union all
8 select 4, NULL from dual ;
4 rows created.
SQL> commit ;
Commit complete.
SQL> select * from state ;
STATE_ID ST
---------- --
1 NY
2 CA
3 AK
4
SQL> select * from state where state_code not in ('NY', 'CA') ;
STATE_ID ST
---------- --
3 AK
SQL> select * from state
2 where state_code not in ('NY', 'CA') and state_code is not null ;
STATE_ID ST
---------- --
3 AK
SQL> select * from state
2 where state_code not in ('NY', 'CA') or state_code is null ;
STATE_ID ST
---------- --
3 AK
4
```
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [May 28, 2010, 6:27pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/38 "2010-05-28T18:27:22Z")
> [@Arnold\_Winkelried](#):
>
> There can only be one row in Hair\_colour for each Person. People that are bald will have 0 rows in table Hair\_colour.
Using a combined pkey/fkey on the hair color table is, indeed, a good solution. (I’ve used this strategy also.)
But consider that in order to use this structure, you have to left join People with Hair Color. The result of that left join is that anyone without a hair color gets a NULL in the result set. So you _still_ have to deal with NULLs; they’re just transient instead of stored. God help you if you do it in a subquery, or try to filter on a boolean operation of Hair Color, without understanding the consequences. 😛
---
### Author:  [@redtail23](https://boards.straightdope.com/u/redtail23)
#### Post date: [May 28, 2010, 6:32pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/39 "2010-05-28T18:32:39Z")
> [@Arnold\_Winkelried](#):
>
> There can only be one row in Hair\_colour for each Person. People that are bald will have 0 rows in table Hair\_colour.
And people for whom you don’t know their hair color will also have 0 rows in the table. So now you don’t actually know if Uncle Otto is bald, or if you just don’t know what color his hair is.
I’d say the way to go would be to have column Hair Colour, and include both “bald” and “unknown” as possible values. Acknowledging that “bald” isn’t _really_ a hair color, but is the closest you can get.
Such is the troubled lot of a database developer. 😛
---
### Author:  [@arseNal](https://boards.straightdope.com/u/arseNal)
#### Post date: [May 28, 2010, 6:34pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/40 "2010-05-28T18:34:54Z")
> [@DanBlather](#):
>
> If that is not the behavior you want, you should have set up your table so that it would not allow NULL in that field. You could have used an empty string.
I’m going to assume you misquoted here …
[Previous page](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959.md?page=1)
[Next page](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959.md?page=3)