In this episode we're going to take a step into the unknown.
It's time to talk about
The null value is a placeholder for missing or not applicable information
Its inventor, Sir Tony Hoare, has famously described it as is billion dollar
mistake. Why?
Because using it brings huge complications to your code.
Without null when you compare two values the result is either true or false. For example
Are these two toys the same color? Yep that's true.
What about these two? Do these weigh the same? No that's false.
But null introduces a third state:
Unknown!
So what happens when you use this?
To make our cuddly toys here we need it to know how much stuffing to put
in them to give them their nice, soft, squishy cuddliness.
But stuffing is an invalid property of bricks.
We're storing both of them in the same table.
So what will we put in a quantity of stuffing column for bricks?
Now we could use zero
But that's not really right. It's an invalid property of bricks.
What we can do to say that it doesn't apply is store null instead.
This brings you headaches when writing your SQL.
Statements only process rows where the where clause is true,
If the result is false or unknown the database doesn't process it
Now if we're asking if the penguin here has the same quantity of stuffing as the brick
this kind of makes sense. Clearly they don't
So the fact that the SQL doesn't return it is alright.
But let's ask a different question
What if we're asking if the penguin has
more stuffing than the brick?
Now intuitively the answer is yes.
The penguin here has loads, the brick here has none.
But if we're saying that this value is greater
than null the answer is unknown and
so the row isn't returned
You can overcome this by using the is null condition
As well as checking whether or not the
penguins here stuffing is greater than the quantity of stuffing for the brick,
you also need to check that the penguins stuffing is not null and that the bricks
is null. Do this and you'll get the correct answer. But if you're comparing
more than a couple of columns this quickly makes your SQL much more complicated.
You can simplify this by mapping nulls to real values
NVL is an Oracle supplied function which returns the second argument if the first is null.
So you can use this to map the quantities of stuffing to zero.
This allows you to compare the columns correctly
Or you can use the ANSI compliant coalesce
This has some subtle differences to NVL but a key one is that
coalesce can take any number of arguments
whereas NVL is limited to just two
There are a host of other null related
functions available which can make working with these values easier
But whatever you do your SQL is more complicated and it's more likely you'll
make a mistake. So perhaps Sir Tony Hoare was right and that null has led to a
billion dollars worth of errors
So we're best off avoiding it but what can you do instead?
In an effort to avoid null many people
use magic values instead. These are special out-of-bounds values which are
impossible in your real data. For example you can't have a negative amount of
stuffing for a brick so to say that the property doesn't apply we can store the
bricks quantity of stuffing as minus one
At first glance this seems to
solve the problems with null If we're comparing the quantity of stuffing for
our penguin the quantity of stuffing for the brick we're back to standard two
valued logic and we get the results we expect
But using magic values can bring
complications to your code elsewhere. For example say we want to sum up the total
quantity of stuffing used for all our toys. You'll subtract one for each brick
you have in your table giving the wrong answer.
Similar problems exist for other aggregate functions such as averages standard deviations
and so on.
While solution is easy, just add a where clause excluding the magic values, this is easy
to forget particularly if they are rare or look like they could be real values in your data
So new developers joining your team may not be aware that these
values have special meaning.
You need to teach everyone joining your team what
your magic values are and how they work so your application will function correctly
At least null is publicly documented and will work the same way
across different systems. You also need to ensure that your impossible magic
values really are impossible. In 1979 Robert Barbour applied for personalized license plates
He entered his first two choices as SAILING and BOATING. He didn't
have a third choice but instead of leaving this line in the form blank
he entered NO PLATE.
It turned out that these first two
choices were already taken and so the DMV issued him with license plates
containing the text NO PLATE
So he ended up with personalized plates that he didn't really want
But the problems don't stop there
It turns out that when
parking officers were issuing tickets for illegally parked vehicles if they
didn't have a license plate they would enter the registration as NO PLATE
And when these tickets were processed and entered into the system they would match
with Robert Barbour's. So he soon started receiving a flood of parking tickets
from all across the country
Remember, if there's any chance that your magic
values turn out to be real valid values you're opening up the possibility of new
even stranger bugs than if you just used null in the first place.
Use these with extreme caution
Dealing with null is a thorny issue. We
covered some of the key points here but as we'll discover in the future episodes
most SQL operations have their own gotchas and caveats when it comes to using null.
Through careful table design can reduce your need to use null
For all columns that should have a real value ensure you add a not null constraint on
them. This will stop null values accidentally creeping into your data
But dealing with missing or not applicable information is a problem
that never really goes away. However you choose to deal with it wether that's null
or magic values ensure that when you test your application you include rows
with these properties
So that was a quick overview of null in Oracle Database
I hope you enjoyed this but, more importantly I hope you learned
something. If you found this useful and like my style then please subscribe to
be informed of future videos or click the links on the screen to see some of
my existing content. And thanks for watching