# 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:** 7
**Page:** 3
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [May 28, 2010, 6:48pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/41 "2010-05-28T18:48:07Z")
> [@redtail23](#):
>
> 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.
That was probably not the best example. Here is another example, let’s say for Credit Card payments:
I will require the billing address for verification (cannot be null). The shipping address is often the same as the billing address but not always.
I could set up my tables this way:
customer
customer\_id PK, name not null, billing\_address not null
customer\_shipping
customer\_id PK, shipping\_address not null
And customer\_shipping would have no rows if shipping\_address is same as billing\_address
But in most cases I would live with the NULL values instead of having a separate table - only a fool will have a fully normalized database.
My example above would probably look like this in reality:
customer
customer\_id PK, name not null, billing\_address\_id not null (FK to address table), shipping\_address\_id null (FK to address table)
address:
address\_id PK, street, city, state\_or\_province, postal\_code, country not null
---
### Author:  [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)
#### Post date: [May 28, 2010, 8:39pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/42 "2010-05-28T20:39:03Z")
> [@Arnold\_Winkelried](#):
>
> That was probably not the best example. Here is another example, let’s say for Credit Card payments:
> I will require the billing address for verification (cannot be null). The shipping address is often the same as the billing address but not always.
>
> I could set up my tables this way:
> customer
> customer\_id PK, name not null, billing\_address not null
>
> customer\_shipping
> customer\_id PK, shipping\_address not null
>
> And customer\_shipping would have no rows if shipping\_address is same as billing\_address
>
> But in most cases I would live with the NULL values instead of having a separate table - only a fool will have a fully normalized database.
>
> My example above would probably look like this in reality:
> customer
> customer\_id PK, name not null, billing\_address\_id not null (FK to address table), shipping\_address\_id null (FK to address table)
>
> address:
> address\_id PK, street, city, state\_or\_province, postal\_code, country not null
The simplest method:
Transaction Table
Billing Address
Shipping Address (application fills in shipping address with billing info if user indicates they are the same, otherwise user must key it)
Any “trickiness” should get resolved once so all down stream operations are less complex, instead of having to re-resolve the shipping address in every application, query, etc. down the road.
---
### Author:  [@Mangetout](https://boards.straightdope.com/u/Mangetout)
#### Post date: [May 28, 2010, 9:16pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/43 "2010-05-28T21:16:51Z")
The hair colour thing is a bit of a red herring, because in practical applications, ‘no hair’ would be a not entirely unreasonable option to include, in the same way that you might have ‘atheist’, ‘no religion’, ‘unknown’ and ‘declined to answer’ in a field intended to store the religious persuasions of the person described. It doesn’t matter that this is an offence against absolute consistency of class in the described characteristic - in the real world, this happens all the time - you either accept and handle nulls, or you make one or more of your list items suitable as a replacement for null.
Dates are a different animal though, because you can’t put ‘n/a’ in a date field, and putting in some fictional date such as 01/01/1900 is guaranteed to eventually come back to bite you on the arse - you’ll end up accidentally congratulating a customer on reaching their 111th birthday, when in fact they’re only 31 and you just didn’t know their DOB.
And storing the date as text is also asking for trouble, because you suddenly lose strong validation on it, and you’ll end up storing a mixture of date formats, and you’ll have trouble calculating the difference between two dates.
You have to either handle nulls, or do something else such as having an additional boolean field to indicate whether the date is known, and test for that when you’re doing anything with dates. Except that’s much harder and considerably less reliable than just dealing with a single column that may contain nulls.
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [May 29, 2010, 1:13am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/44 "2010-05-29T01:13:42Z")
> [@RaftPeople](#):
>
> Any “trickiness” should get resolved once so all down stream operations are less complex, instead of having to re-resolve the shipping address in every application, query, etc. down the road.
If there is some tricky way of resolving the shipping address, that can be built into a view, and the SELECT statements in the application can select from the view.
---
### Author:  [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)
#### Post date: [May 29, 2010, 2:25am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/45 "2010-05-29T02:25:33Z")
> [@Arnold\_Winkelried](#):
>
> If there is some tricky way of resolving the shipping address, that can be built into a view, and the SELECT statements in the application can select from the view.
True, but what exactly is being gained by not explicitly storing the shipping address? There is a definite gain if you do explicitly store it and that is reduced complexity and reduced knowledge required to access the information (don’t need to remember to use the view) and thus fewer errors later on.
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [May 29, 2010, 3:34am UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/46 "2010-05-29T03:34:39Z")
What is gained is avoiding the duplication of information - if the billing address is the same as the shipping address, and they are both stored in the database, then whenever you change one you have to remember to change the other.
---
### Author:  [@DanBlather](https://boards.straightdope.com/u/DanBlather)
#### Post date: [May 29, 2010, 5:26pm UTC](https://boards.straightdope.com/t/why-does-sql-assume-you-dont-want-to-indlude-nulls-in-queries/540959/47 "2010-05-29T17:26:46Z")
:smack:
> [@arseNal](#):
>
> 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=2)