SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Database theory

What if null if null is null null null is null?

Hugo Kornelis — Sun, 30 Sep 2007 20:19:30 GMT

In this fourth and final part in my series about NULL, I’ll discuss some well-known and some less well-known functions and keywords that are specifically created to deal with NULL values. And I will, of course, explain why null if null is null null null is null. In case you have not yet read them, you can click these links to read the first, second, and third part.

IS NULL is not = NULL

I have already explained why tests for equality with NULL will always return Unknown instead of True or False. This holds true in all circumstances – even when both sides of the equation are NULL. That’s why the query below will not help you find the people for which no birthday is on file:

SELECT FirstName, LastName

FROM dbo.Persons

WHERE Birthday = NULL;

Even though no birthday is on file for Hugo Kornelis, my name will not be returned – because when evaluating “my” row, SQL Server will see a comparison between a missing value (my birthday) and a missing value (the NULL) – and when asked whether two unspecified values are equal, the only guaranteed correct answer is “I ain’t the faintest, dude” (for the British readers, that would be “I haven’t got the foggiest idea, old chap”).

Since there are numerous occasions where you want to find rows where values are missing, a special operator was introduced for this: IS NULL. Rewriting the query above as follows will return my name, because my Birthday is indeed NULL in this database.

SELECT FirstName, LastName

FROM dbo.Persons

WHERE Birthday IS NULL;

Note that the operator IS NULL will return True if the operand (Birthday in this case) is NULL, or False if it is any other value – an IS NULL test can therefore never result in Unknown. So to find values that are not missing, the expression can simply be negated to

WHERE NOT(Birthday IS NULL);

but there is also a shorthand form available that is much more common – in fact so much more that I don’t think I’ve ever seen the above version used!

WHERE Birthday IS NOT NULL;

Do not forget that the NULL keyword is an integral part of the IS [NOT] NULL operator. You can’t use IS or IS NOT as a replacement for = and <> and expect the same special treatment of NULL values that IS [NOT] NULL provides. That would require the use of the IS [NOT] DISTINCT FROM operator that is specified in SQL:1999 but not yet implemented in SQL Server (vote here if you’d like to see this changed!)

SET ANSI_NULLS { ON | OFF }

Some of you may have seen existing code where a predicate such as Column = NULL is used, and does actually return rows. This is probably legacy code that has not been maintained in a long time (and if it isn’t, it has to be written by a legacy developer who has not maintained his knowledge in a long time).

Very early versions of SQL Server were released before agreement was reached in the ANSI committee on all features. As a result, some features were implemented in a different way than what the ANSI committee turned out to describe, facing Microsoft developers with the challenge to upgrade to ANSI compliant behaviour without breaking existing code.

Behaviour of NULL comparisons in predicates is one such example where the original version of SQL Server “got it wrong”. So when SQL Server was changed to return Unknown on a comparison with NULL, the SET ANSI_NULL OFF option was introduced to force the “old” behaviour that would return False on NULL vs. non-NULL comparison, and True on NULL vs. NULL comparison.

The SET ANSI_NULL option has been marked as deprecated in SQL Server 2005, so if you find code that still relies on this setting, you’d better change it ASAP. This would of course be very easy if you only had to look for “= NULL” and “<> NULL”, and change them to “IS NULL” and “IS NOT NULL” – but unfortunately, expressions of the form “= expression” where expression can be NULL are also affected by the ANSI_NULL setting; these are much harder to identify or fix!

ISNULL is not COALESCE

Despite the name similarity, and despite the fact that in Access, ISNULL(xxx) is equivalent to xxx IS NULL, the T-SQL ISNULL function is completely different from the IS NULL predicate. ISNULL is used to return the first non-NULL from its two inputs. However, ISNULL is also a leftover from the days before the ANSI standard was finalised – the ANSI standard function that should be used to replace ISNULL is called COALESCE. Despite the similarities, there are also a couple of differences that you should be aware of before replacing all your occurrences of ISNULL to COALESCE.

One reason to prefer COALESCE of ISNULL, in addition to its adherence to standards, is that COALESCE can take any number of arguments whereas ISNULL only takes two. Both will return the first non-NULL argument, or NULL if all arguments are NULL. The unlimited number of arguments makes COALESCE a far better option when tasked to find the first non-NULL value from more than two inputs – for example, to find the first non-NULL of Arg1, Arg2, Arg3, and Arg4, I’d rather use

COALESCE(Arg1,Arg2,Arg3,Arg4)

than

ISNULL(Arg1,ISNULL(Arg2,ISNULL(Arg3,Arg4)))

Another major problem with ISNULL is how it deals with implicit conversions. COALESCE will, like any other T-SQL operation, use the rules of data type precedence to find the data type of its result – but ISNULL will always return a value of the data type of its first argument, making this the only T-SQL keyword that does not respect the precedence rules.

Unfortunately, there is one situation where you can not simply rip out ISNULL and replace it with COALESCE – and that is in the case of computed columns. If you attempt to create the tables below, you will see that Test_ISNULL is created without problems, whereas the attempt to add the index to Test_COALESCE fails.

CREATE TABLE Test_ISNULL

(Col1 int NOT NULL,

Col2 int NULL,

Col3 AS ISNULL(Col2, Col1) PRIMARY KEY);

CREATE TABLE Test_COALESCE

(Col1 int NOT NULL,

Col2 int NULL,

Col3 AS COALESCE(Col2, Col1) PRIMARY KEY);

I consider this to be a bug. Clearly, a computed column using COALESCE should inherit its nullability from the last argument, just as is the case with ISNULL. Please vote on Connect if you agree with me that this bug should be fixed, as a first step towards deprecating and removing the superfluous and confusing ISNULL function.

NULLIF, the forgotten one

The last NULL related keyword that I will cover here is also the least well-known (although some strange coincidence cause Jeff Smith to blog about it earlier this week). That may be due to the fact that it is only useful in a limited number of cases, and also not as easy to understand as the other ones.

The NULLIF function takes two arguments. It returns NULL if the first argument is equal to the second one – in all other cases, the first argument is returned unchanged. According to the SQL standards, NULLIF is actually a shorthand form for a CASE expression:

NULLIF(Arg1, Arg2)

is defined as equivalent to

CASE WHEN Arg1 = Arg2 THEN NULL ELSE Arg1 END

The most common use of NULLIF is to prevent runtime errors such as division by zero. For instance, the query below will fail if there are any rows with (Col1 + Col2) equal to zero:

SELECT IdCol, Col1 / (Col1 + Col2) AS Col1Ratio

FROM dbo.SampleTable;

One way to attempt to prevent this error is to exclude these rows:

SELECT IdCol, Col1 / (Col1 + Col2) AS Col1Ratio

FROM dbo.SampleTable

WHERE Col1 + Col2 <> 0;

However, this might fail because SQL Server might use an execution plan that calculates Col1Ratio before applying the WHERE clause – that is not a bug, the SQL standards explicitly allow the vendors such freedoms in the implementation of their products. And if other columns are displayed as well, completely omitting the rows is not a good way to tackle this problem.

This is where NULLIF comes to the rescue. In the query below, the divisor will be changed to NULL if it was 0, setting the result of the entire calculation to NULL instead of causing a runtime error.

SELECT IdCol, Col1 / NULLIF(Col1 + Col2, 0) AS Col1Ratio

FROM dbo.SampleTable;

If so desired, a COALESCE function can be added to change this NULL result back to a chosen numeric value – or it can be left as NULL to make it instantly visible that for this row, a value of Col1Ratio could not be computed.

Another problem area where NULLIF can be used is to check if values are distinct. I have already explained the difference between unequal and distinct in the third part of this series, and I have also presented this method for testing for distinctness:

WHERE OldValue <> NewValue

OR (OldValue IS NULL AND NewValue IS NOT NULL)

OR (OldValue IS NOT NULL AND NewValue IS NULL);

This is of course rather clumsy; many developers prefer to replace this with a shorter expression. That is very easy to do if there is some magic value that OldValue and NewValue can never be equal to – for instance, if both are always above 0, you could replace the three lines above with this single line:

WHERE COALESCE(OldValue, -1) <> COALESCE(NewValue, -1)

But what if any value can occur in the domain? In that case, you can use NULLIF to shorten the expression a bit:

WHERE NULLIF(OldValue, NewValue) IS NOT NULL

OR NULLIF(NewValue, OldValue) IS NOT NULL

Remember that NULLIF returns NULL if both arguments are equal, but also if the first argument is NULL. So for the expression above, both NULLIF expressions will be NULL if either NewValue is equal to OldValue, or both are NULL. If one of the two is NULL and the other is not, only one of the NULLIF expressions will be NULL. If neither is NULL and they are not equal, both NULLIF expression will be NOT NULL.

Do remember that neither of the three distinction tests presented above will allow the use of an index seek strategy. Until Microsoft implement IS [NOT] DISTINCT FROM, we’ll have to live with not being able to use an index seek for this.

What if null if null is null null null is null?

Now, I can finally answer this question – I only have to remove some spaces and add some commas and parentheses to change it to valid SQL Server syntax:

IF NULLIF(NULL, ISNULL(NULL, NULL)) IS NULL

Note that this is not a valid statement, as the IF statement misses its statement block. Don’t add it yet – first try to predict the results without running the code.

The result of ISNULL(NULL, NULL) should of course be NULL, since both arguments are NULL. That simplifies the IF statement to:

IF NULLIF(NULL, NULL) IS NULL

The result of NULLIF(NULL, NULL) should be NULL. Not because the two operands are equal (they are not – remember, NULL = NULL evaluates to Unknown!), but because in case of unequal operands, the first argument is returned. So now we have:

IF NULL IS NULL

which will of course evaluate to True.

Now complete the IF statement in the original query and run it to check our prediction:

IF NULLIF(NULL, ISNULL(NULL, NULL)) IS NULL

PRINT 'Prediction was correct';

ELSE

PRINT 'I goofed...';

Msg 8133, Level 16, State 1, Line 1

None of the result expressions in a CASE specification can be NULL.

So I didn’t predict the result correctly, nor did I goof … instead, I discovered an interesting caveat with NULLIF – or rather, with the CASE expression that this NULLIF expression expands to:

CASE WHEN NULL = NULL THEN NULL ELSE NULL END

The problem here, is that both the THEN (or rather, each THEN, as a CASE expression allows as many WHEN … THEN clauses as you wish) and the ELSE clause return the constant expression NULL. That this is the problem is easily verified:

SELECT CASE WHEN 1 = 2 THEN NULL ELSE NULL END

Msg 8133, Level 16, State 1, Line 1

None of the result expressions in a CASE specification can be NULL.

This error message is misleading. Result expressions of a CASE specification are allowed to be NULL; I’ve used that many times. The real reason that this particular query errors is related to how SQL Server determines the data type for a constant in the query. A numeric constant, like 42 or 3.14159265, is considered to be integer or numeric(9,8); a constant enclosed in quotes like ‘this’ is considered to be varchar(4). But since any data type allows the NULL “value”, the constant NULL can be of any data type and SQL Server has no way of guessing what you mean. This is usually solved by checking what data types are used around it. So in the expression

CASE WHEN 3 = NULL THEN NULL ELSE '3' END

the first NULL is considered to be of data type integer (as it is compared to the constant 3, which is integer as well), and the second NULL is considered to be varchar(1) (as both this NULL and the constant ‘3’ are possible results of the CASE expression).

Back to the error query – if all possible result expressions of the CASE expression are the constant NULL, SQL Server has no way of working out the data type of the result expression, and that is indeed an error – so SQL Server did the right thing in returning an error, it just chose a bad message! Interestingly, even though COALESCE is also defined as shorthand for a CASE expression, there is a specific error message for COALESCE(NULL,NULL) in SQL Server 2005 (maybe as a result of this connect entry?) – but instead of fixing the real problem, Microsoft chose to fix only the specific COALESCE case. I have of course filed a bug report on Connect for this misleading error message.

Anyway, now that I know the cause of the error message, I can also fix it, and run the code below to finally check if null if null is null null null is null:

-- Please, NEVER use this as a variable name in real code....

DECLARE @NULL int;

-- This superfluous assignment makes the code self-documenting.

SET @NULL = NULL;

-- Now check if null if null is null null null is null:

IF NULLIF(@NULL, ISNULL(@NULL, @NULL)) IS NULL

PRINT 'Prediction was correct';

ELSE

PRINT 'I goofed...';

Prediction was correct

Dr. Unknown, or how I learned to stop worrying and love the NULL

Hugo Kornelis — Sat, 22 Sep 2007 20:35:00 GMT

Two months ago, I posted the first two parts of a series about NULL. After that, I went quiet. Much to do and little time was one excuse. But to be honest, I also lost interest. However, I felt I owe my readers to conclude the series, so I have now forced myself to write and publish the last parts before moving on to more interesting subjects J.

Before reading on, I suggest that you first read (or reread) the first and second part of this series, so that we’re all on the same level again.

Finished reading? Okay, good to see you back. J

How to turn logical into illogical

In his comment to the second part of this series, David Portas pointed out the danger of equating the behaviour of Unknown in SQL’s three-valued logic to the behaviour of humans when confronted with unknown (or rather: missing) data. The problem is that the behaviour of NULL and Unknown is defined such that it will mostly produce the logical result – but that defined behaviour is then in true computer-fashion applied to all situations. A computer lacks the brain power to assess when the rules stop to make sense.

Here are two examples where the logical treatment of NULL and Unknown, when applied to a specific situation, renders completely illogical results:

DECLARE @Var int;

SET @Var = NULL;

IF @Var = 8 OR @Var <> 8

PRINT 'It either is or isn''t 8';

ELSE PRINT 'Huh? It''s not 8, but it''s not not 8 either!';

IF @Var = @Var

PRINT 'Duh, of course it''s equal to itself';

ELSE PRINT 'What the hey, it''s not even equal to itself!!';

Both conditions in the code above are immediately recognisable as tautologies for us humans. But SQL Server doesn’t attempt to identify tautologies, it just does as instructed. So in the case of the first IF statement, it first evaluated each of the individual parts: “@Var = 8” is Unknown, since the value for @Var is missing and might or might not be 8. Likewise, the evaluation of “@Var <> 8” results in Unknown as well. And then the logic table that I explained in my last post is consulted to get a result of Unknown for “Unknown OR Unknown” – and hence the ELSE is executed (and the PRINTed output should actually have read “I’m not really sure whether it’s either equal or unequal to 8!” – which of course makes no sense either). Note also that changing “@Var <> 8” to “NOT(@Var <> 8)” will not change the outcome.

The second example takes the oddity yet a step further. Surely, anything is always to itself? Well, yeah, sure it is – but the specifications of the ANSI standard disallow SQL Server from knowing that. What SQL Server does here, is that first the variable reference is replaced by its value, so that the equation to evaluate becomes “NULL = NULL” – and there is of course no way to know if two missing values are equal to each other or not.

A different, slightly better hidden form of tautology is presented in this example:

CREATE TABLE Test

(Col1 int NOT NULL PRIMARY KEY,

Col2 int NULL,

CHECK(Col2 < 10));

INSERT INTO Test (Col1, Col2)

SELECT 1, 3

UNION ALL

SELECT 2, NULL;

SELECT Col1, Col2

FROM Test

WHERE Col2 < 10;

For the row with Col1 = 2, the value of Col2 is missing – but because of the CHECK constraint, we know that it can never be 10 or more. And yet, SQL Server had to reject this row, based on the handling of NULL that I have been labelling as “logical” in my previous posts!

An idea that pops up in newsgroups from time to time, is to have the RDBMS recognise tautologies and react accordingly, regardless of whether the input is NULL or not. I must say that I am glad that this has, so far (touches wood), not been incorporated in either the ANSI standard or SQL Server. Sure, I do appreciate that the current situation leads to weird effects, but it is at least 100% logical. But extending the database with a list of recognised tautologies would introduce another weirdness, for I consider it to be highly unlikely that a database will ever be able to recognise all possible tautologies. Would you instantly recognise the WHERE clause below as a tautology?

WHERE Foo BETWEEN 0 AND Bar

OR SQUARE(Foo) > Foo * Bar

OR Bar <= 10;

And remember – even if you could, and if you could make a query parser do it too, I can always come up with a more complex way of saying “True”.

Unequal, but not distinct

Database developers often have to deal with testing if values are unequal – but they also have to deal with testing if values are distinct. Now you may think that “distinct” is the same as “unequal”, and you may even find that Wikipedia agrees with you – but in database land, “distinct” is in fact both distinct from and unequal to “unequal”.

The difference between equality and distinction, in database terms, is related to NULL values. For an equality comparison, the result is Unknown if either or both the arguments is NULL, as explained previously. For a distinction comparison, though, a NULL value is considered to be distinct from any non-NULL value, but the same as another NULL. It is easy to see this in practice – just run a query with a GROUP BY or a DISTINCT clause, and you will see that all NULL values are lumped together in a single group. (Unfortunately, Microsoft has also seen fit to use distinction rather than equality in its implementation of UNIQUE constraints, a clear violation of the ANSI standard that often forces us to implement awkward workarounds – vote here if you agree with me that this should be changed).

There are various occasion where a database developer will want to know if two values are distinct. A typical example would be a trigger to track changes and store them in a separate auditing table – if only two columns in a 40-column table are actually changed, you prefer not to waste log space on the other 38 columns. But a straight comparison between the old and the new value would only catch changes from a non-NULL to a different non-NULL value; changes from NULL to non-NULL and vice versa would not be audited since this comparison would result in Unknown. As would a non-change of a NULL value.

Unfortunately, Microsoft has not yet implemented the IS [NOT] DISTINCT FROM operator that was added in the SQL:1999 version of the ANSI standard. That means that instead of being able to write

WHERE OldValue IS DISTINCT FROM NewValue;

we are stuck with writing this more clumsy (and less efficient) variant:

WHERE OldValue <> NewValue

OR (OldValue IS NULL AND NewValue IS NOT NULL)

OR (OldValue IS NOT NULL AND NewValue IS NULL);

Implementing the IS [NOT] DISTINCT FROM clause has been suggested on Microsoft Connect two months ago by Steve Kass. There are already 49 votes in favour of this suggestion and Microsoft is considering it for SQL Server 2008 – you too can increase the chance that this will happen by adding your vote here.

WHERE WeAre [NOT] IN (trouble)

Another common cause of NULL-related woes is related to the use of [NOT] IN with a subquery. To understand the root cause of this troubles, it’s useful to first have a look at the exact meaning of [NOT] IN with a hardcoded value list.

In the ANSI standard, the IN clause is defined such that

WHERE Something IN (3, 5, 7);

should be considered as shorthand for

WHERE Something = 3

OR Something = 5

OR Something = 7;

and should therefore return the exact same result. Likewise,

WHERE Something NOT IN (3, 5, 7);

should return the same result as

WHERE NOT (Something = 3

OR Something = 5

OR Something = 7);

WHERE Something <> 3

AND Something <> 5

AND Something <> 7;

This definition is to be taken very literally. Even when a NULL is inserted in the value list, the definition still holds. So

WHERE Something IN (3, NULL, 7);

is considered as shorthand for

WHERE Something = 3

OR Something = NULL

OR Something = 7;

and returns the same results. If Something = 3, the result will be True (True OR Unknown OR False) and the row is included. If Something = 2, the result is Unknown (False OR Unknown OR False), and the row is not included. No surprises so far. And if Something is NULL, the result ends up as Unknown (Unknown OR Unknown OR Unknown) as well, so this row is excluded as well – this may be slightly surprising, but not really a big problem for most.

The nasty surprise sits in the NOT IN variant:

WHERE Something NOT IN (3, NULL, 7);

is shorthand for

WHERE Something <> 3

AND Something <> NULL

AND Something <> 7;

and this will never return any row at all! See, if Something is 3, the result is of course False (False AND Unknown AND True) and the row is omitted, as expected. For a NULL value of Unknown, the result is Unknown (Unknown AND Unknown AND Unknown), so this row is omitted as well. But the kicker is what happens if Something = 2 … in that case, the result is not True (as most would expect), but Unknown (True AND Unknown AND True) and this row is also omitted from the result. After all, the database can’t be sure that the missing value is accidentally equal to 2, and doesn’t want to risk returning rows that don’t qualify!

Mind you, I have never ever seen anyone actually use a NULL in the hardcoded value list of a NOT IN clause, so what’s the deal anyway? Well, it’s simple – the defined behaviour of a [NOT] IN (subquery) clause is to evaluate the subquery, replace it with the list of values returned from the subquery, and then evaluate the [NOT] IN according to the rules discussed above. (Note that the query optimizer is free to, and will often, choose another method of actually executing the query as long as it doesn’t change the results). So if Foo.Bar can be NULL, the result of

WHERE Something NOT IN

(SELECT Bar

FROM Foo

WHERE Yadda yadda);

will be just as empty as the result of

WHERE Something NOT IN (3, NULL, 7);

The easy workaround is of course to explicitly exclude the NULLs from the subquery:

WHERE Something NOT IN

(SELECT Bar

FROM Foo

WHERE Yadda yadda

AND Bar IS NOT NULL);

A different solution is to rewrite the NOT IN to a subquery with NOT EXISTS:

WHERE NOT EXISTS

(SELECT *

FROM Foo

WHERE Yadda yadda

AND Bar = Something);

This also provides a nice workaround for the current non-existing support for row value constructors in SQL Server (also on Connect, so vote here if you deem this important). You can not write

WHERE (Something, Splunge) NOT IN

(SELECT Bar, Splat

FROM Foo

WHERE Yadda yadda);

but you can use this:

WHERE NOT EXISTS

(SELECT *

FROM Foo

WHERE Yadda yadda

AND Bar = Something

AND Splat = Splunge);

My advice is to strictly avoid [NOT] IN (subquery) and always use [NOT] EXISTS (subquery) instead. Not only because the latter avoids the NULL issues, but also because [NOT] EXISTS can be used in cases where [NOT] IN isn’t possible. Plus, I have never seen a case where [NOT] IS performs better than [NOT] EXISTS (but I have seen the reverse).

There is of course nothing wrong with using [NOT] IN (value, value, …)

What if null if null is null null null is null?

I promised to answer this question – and now I see that this is already my longest blog post ever, and I’m not even near the answer yet. So I’ll just go ahead and tell you that, if null if null is null null null is null, everything is okay and the product is working as it should be, but for one caveat. Why, and what caveat, that’ll have to wait until I find the time to wrap up and post the final part of this series on NULL. (And no, I won’t keep you waiting for another two months – promise!)

The logic of three-valued logic

Hugo Kornelis — Tue, 17 Jul 2007 20:58:00 GMT

In my previous post, I explained what NULL does and does not mean, how and why the rule of NULL propagation forces any expression involving NULL to result in NULL. I also mentioned that comparisons involving NULL do not result in NULL but, rather, in a new “truth value” called “Unknown”. But there’s a lot more to be written about Unknown and it’s effect on logical expressions, and that’s exactly what this blog post (and the next one as well) will be about.

Most software developers will be intimately familiar with what is popularly known as Boolean logic but should actually be called a two-valued Boolean algebra – a Boolean algebra that is defined on the two truth values True and False, and the three operators AND, OR, and NOT. However, with the introduction of Unknown as a third truth value, some rules of Boolean algebra are violated, so the original two-valued Boolean algebra has to be replaced by a new, non-Boolean three-valued algebra for logical operations. I’ll assume that most readers already know the basics of this three-valued logic, but I’ll briefly cover them anyway (in this post) before moving on to the more interesting side effects (mostly in a future post).

Truth tables for AND, OR, and NOT

Since we have already established that a proposition such as WHERE Age > 35 can result in Unknown, the logical next step is to define how this affects the result of propositions such as WHERE Age > 35 AND Country = 'USA', WHERE Age > 35 OR Country = 'USA', and WHERE NOT (Age > 35). In other words, we have to determine how the new truth value of Unknown affects the truth tables for the AND, OR, and NOT operators. (Note that some sources, including Books Online, label these operators as “Boolean” operators. This is not correct, since the T-SQL versions of these operators operate on three-valued logic rather than the Boolean two-valued logic.)

I believe that simple, easy-to-follow examples are the best way to illustrate a complex subject, so that is exactly how I will explain the logic behind the truth tables for AND, OR, and NOT – by using a simple example. I’ll just stick with my previous example that revolves around my undisclosed age, since that saves me the hassle of not disclosing other personal information.

Let’s look at the AND operator first. In two-valued logic, True AND True evaluates to True, whereas True AND False, False AND True, and False AND False all evaluate to False. The additional truth value of Unknown means that we have to fill in five new cells, for True AND Unknown, False AND Unknown, Unknown AND True, Unknown AND False, and Unknown AND Unknown.

WHERE Age > 35 AND Country = 'USA'

Many readers will probably already know that I’m Dutch (and those that didn’t, do now), so my country of residence is definitely not the USA. If someone points a gun to your head and demands that you tell him or her whether or not it is true that Hugo Kornelis is over 35 years of age and living in the USA what will your answer be? You’ll probably answer that this proposition is false – since you now know that I’m not living in the USA, you don’t need to know my age anymore; regardless of my age, the proposition as a whole can only be false.

Congratulations – you have just filled in two of the missing cells in the truth table for AND: both Unknown AND False and False AND Unknown evaluate to False.

WHERE Age > 35 AND Country = 'Nederland'

Assuming you know that “Nederland” is Dutch for “the Netherlands”, you’ll find this proposition harder to assess. Okay, so the second part is true. But the proposition as a whole is only true if I’m also over 35, which you obviously don’t know. So if once again someone puts that proverbial gun against your head, you can only reply that you really don’t know, and hope that this is enough to convince the shooter not to pull the trigger.

For the truth table, we can now fill in that Unknown AND True (and True AND Unknown) evaluate to Unknown.

WHERE Age > 35 AND Age < 45

This leaves us with just one missing cell in the truth table for AND, for Unknown AND Unknown. The proposition above illustrates this one. Since you don’t know my age, you don’t know if I’m over 35, nor if I’m under 45 – so you have obviously no way of telling whether I’m both over 35 and under 45.

The only logical outcome of Unknown AND Unknown is Unknown.

The truth table of OR also starts with the four cells know from two-valued logic: True OR True, True OR False, and False or True evaluate to True, and False OR False evaluates to False. The same five new cells that were added to the table for AND are added here as well, and we’ll use similar examples to populate them.

WHERE Age > 35 OR Country = 'USA'

Since I still haven’t moved to the USA, the result of this proposition is True if I’m over 35, or False if I’m not. Since you don’t know my age, you don’t know the result of this proposition.

Unknown OR False and False OR Unknown both evaluate to Unknown.

WHERE Age > 35 OR Country = 'Nederland'

Here’s an example where you don’t have to fear the wrath of the shooter. No matter what my age is, you can safely answer that, since I’m living in the Netherlands, it is also true that I am over 35 years of age or living in the Netherlands.

Both Unknown OR True and True OR Unknown evaluate to True.

WHERE Age > 35 OR Age < 30

(Those who have ever seen me will have to stretch their imagination a bit. Just pretend that you really can’t tell for sure that I’m definitely not under 30, to make this a proper example of Unknown OR Unknown, ’kay?)

Not much to be said about this example - Unknown OR Unknown is still Unknown.

Now that the truth tables for AND and OR are complete, we only have the table for NOT left. This one grows from two cells (NOT True = False, and NOT False = True) to three, the third cell being for NOT Unknown. So, here’s the example:

WHERE NOT Age > 35

You don’t know my age. That’s why you don’t know whether I’m over 35. So, how can you possibly know whether I am not over 35? Duh!

NOT Unknown is, of course, Unknown.

So, to wrap things up, here are the completed truth tables for the operators AND, OR, and NOT in three-valued logic

The truth table for AND:

	True	Unknown	False
True	True	Unknown	False
Unknown	Unknown	Unknown	False
False	False	False	false

The truth table for OR:

	True	Unknown	False
True	True	True	True
Unknown	True	Unknown	Unknown
False	True	Unknown	false

The truth table for NOT:

True	False
Unknown	Unknown
False	True

Some oddness

If you ever forget the logic tables, you can use examples similar to these to easily reconstruct them on the spot. However, do take care that you use independent propositions. If you don’t, you can easily get at incorrect conclusions, as illustrated by these propositions:

WHERE Age > 35 AND Age < 30

WHERE Age > 35 OR Age < 45

This is a fine example of the difference between humans and computers. Humans think; computers only do as told. You do not need to know my age in order to tell everyone who asks you that Hugo Kornelis is definitely not both over 35 and under 30, or that he definitely is either over 35 or under 45. A computer lacks this logic insight – it just does as told. The instructions for the computer are clear: first evaluate the individual propositions (all resulting in Unknown, since each of the propositions by itself is indeed Unknown), then apply the AND or OR operator to the two truth values – so the computer will return Unknown for the two propositions above, even though every sane human knows that they should evaluate to False and True respectively.

CHECK, the odd one out

Once a proposition has been evaluated to a single truth value, the computer has to determine what to do with the result. In most all places where the SQL language rules allow a logical expression, the defined behavior is to treat True one way, and both False and Unknown in another way. For instance, a row is included in the result set of a query if the WHERE clause evaluates to True, but excluded if it evaluates to either False or Unknown. Same goes for the decision to include or exclude a group of rows based on the HAVING clause – it has to evaluate to True; both Unknown and False means that the group is out. The query processor prefers to be safe rather than sorry – if it can’t tell for sure that you want the row included, it won’t be.

For logical expressions in IF and WHILE statements and in CASE expressions, the same logic holds. A condition statement introduced with IF will be executed if the condition is True whereas the ELSE part (if any) is executed on both False and Unknown; a WHILE block will be repeated as long as the expression evaluates to True and execution stops on False and Unknown; and in a CASE expression, the WHEN that evaluates to True determines the result.

There is one notable exception to this – the CHECK constraint. If a row is inserted or updated that causes the CHECK constraint to evaluate to True, the modification is accepted; if it evaluates to False, it is rejected – but if it evaluates to Unknown, the modification is accepted! This is an unexpected change from the accustomed, but not one that has been made without reason – since Unknown usually results from a NULL value in the constrained column, rejecting rows if the CHECK constraint if the proposition evaluates to Unknown would merely duplicate the effect of a NOT NULL constraint. With the definition of a CHECK constraint as it is, illogical as it might seem, it’s very easy to define both an optional age column that will only accept ages over 35 or no age at all, and equally easy to define a mandatory age column that forces you to enter an age of over 35:

CREATE TABLE DemoPersons

(PersonID int NOT NULL

,AgeOptional tinyint NULL CHECK (AgeOptional > 35)

,AgeMandatory tinyint NOT NULL CHECK (AgeMandatory > 35)

,CHECK (AgeOptional = AgeMandatory)

);

Note the third check constraint, which ensures that both age columns are populated with the same age – but only if both age columns are populated at all, since this expression also results in Unknown if either of the age columns is NULL.

More oddness

The different treatment of Unknown in a CHECK constraint versus in a WHERE clause (or anywhere else, for that matter) can result in some interesting behavior. Let’s add some rows to illustrate this.

INSERT INTO DemoPersons (PersonID, AgeOptional, AgeMandatory)

SELECT 1, 36, 36

UNION ALL

SELECT 2, NULL, 37

UNION ALL

SELECT 3, 38, 38

UNION ALL

SELECT 4, 39, 39

UNION ALL

SELECT 5, NULL, 40;

Now, since the check constraint excludes row with an age of 35 or less, the query below should return 0, right? Well, no. It returns 2.

SELECT (SELECT COUNT(*)

FROM DemoPersons)

- (SELECT COUNT(*)

FROM DemoPersons

WHERE AgeOptional > 35);

It appears odd, on first sight, that a table with a CHECK constraint that only allows ages of over 35 still allows for rows that do not have an age of over 35 – but it does, in fact, make perfect sense. Remember that rows for which AgeOptional > 35 is Unknown are admitted by the CHECK constraint, but rejected by the WHERE clause!

In the next post on NULL, I’ll show more seemingly odd behavior of NULL, and explain why this behavior is, in fact, not odd at all. I’ll also answer the question if null if null is null null null is null – I bet you can’t wait!

NULL - The database's black hole

Hugo Kornelis — Thu, 05 Jul 2007 21:20:00 GMT

I once read a scientific article on black holes that started of on the observation that, since a hole is the absence of anything and black is invisible in space, a black hole is in fact an invisible nothing – so what the heck are we talking about? Well, almost the same can be said about NULL in databases.

Let’s first take a look at what NULL is supposed to be. Here is the definition of NULL from the SQL-2003 standard:

null value: A special value that is used to indicate the absence of any data value.

This definition differs significantly from some common misconceptions about NULL:

· NULL is not the same as the numeric value 0, even though they are pronounced the same in some languages (like Dutch or German).

· NULL is also not the same as the string value ‘’ (also known as the empty string), even though many Oracle developers would like to believe so. However, they can’t be blamed for this as this is completely Larry Ellison’s fault.

· NULL is definitely not the same as either of the date values January 1^st 1753, January 1^st 1900, or December 31^st 9999, even though there might be valid (performance related) reasons to use either of those values as a magic value instead of NULL in a specific situation.

· Moving to the more controversial stuff, NULL does not mean “not applicable”. Of course, a NULL in a table is often a result of the attribute not being applicable for a specific occurrence of the entity stored in the table (e.g., a column “birthday” in a customer table that stores details of businesses as well as humans) – but in other columns and other tables, or even in another row of the same table, there might be a completely different reason for the data being missing (absent)!

· And saving the best (read: most controversial) for the last, NULL is also definitely not meant to signify “unknown”. Again, a NULL in a table might result from the value being unknown at data entry time (e.g., when we forget to ask a customer for his or her birthday), but there might be other reasons as well. Unfortunately, many text books insist on explaining the behaviour of NULL in expressions by describing NULL as unknown, rather than missing or absent, causing this misconception about NULL to be the most widespread and the hardest to combat.

Allow me to dwell some more on this whole unknown/not applicable thing, as it’s able to confuse even highly-appraised scientists – as is aptly demonstrated in “Much Ado About Nothing”, an exchange between Dr. E.F. Codd and C.J. Date about NULL, in which Date gets Codd to agree that there should be more than one kind of NULL. The fact that both Codd and Date apparently missed, is that the examples used in their debate failed to meet the basic rules of normalization! Moving back to the birthday example to illustrate this, it is true that NULL in this column can have different causes – but that doesn’t change the meaning of this NULL, which is limited to “the birthday for this particular customer is not in the database”. If the business doesn’t care why a birthday is missing, then the fact that there might be different causes doesn’t have any consequences on the database. If, on the other hand, the business does case about the reason that a birthday is missing, then this reason should of course be modeled and stored in the database – but not in the same column as the birthday! “Birthday” is one attribute and “Reason birthday is missing” is a different attribute – heck, they even have completely disjunctive domains! The fact that these attributes are mutually exclusive doesn’t warrant violating first normal form by stuffing these two attributes in a single column! In this case, a proper design for a SQL Server table would look like this:

CREATE TABLE Customers

(CustomerID int NOT NULL,

Birthday datetime NULL,

ReasonNoBirthday int NULL,

-- Other columns,

CONSTRAINT PK_Customers PRIMARY KEY (CustomerID),

-- Time part for birthday has to be midnight

CONSTRAINT CK_Birthday CHECK

(CONVERT(char(8),Birthday,108)='00:00:00'),

-- Birthday mutually exclusive with ReasonNoBirthday

CONSTRAINT CK_ReasonNoBirthday CHECK

((Birthday IS NULL AND ReasonNoBirthday IS NOT NULL)

OR (Birthday IS NOT NULL AND ReasonNoBirthday IS NULL))

);

Things get more complex when nullable columns are used in expressions and predicates. In a procedural language, this wouldn’t have been a problem – if a procedural program fails to find the information it needs, it enters a conditional branch to handle this situation, as defined by the programmer. In a declarative, set-based language such as SQL, this was not possible. The alternatives were either to have the SQL developer add conditional expressions for each nullable column in a query to handle missing data, or to define a decent default behavior in SQL for missing data so that developers only have to write explicit conditional expressions if they need to override the default behavior.

The default NULL handling in expressions is very much based on how humans would handle similar situations. For instance, stop and think a moment what you would reply if I asked you to calculate my age, and to mimic database behavior, I’d also constrain your answer to be within the domain of integer values. You’d probably first ask me for my birthday. But if I refuse to specify my birthday, you would be unable to answer the question – so you wouldn’t answer it at all. And this is exactly what a database does – if any value to be used in an expression is missing (NULL) , there won’t be any result of the expression; in other words, the result is missing (NULL) as well. This is known as the rule of NULL propagation: any expression in SQL returns NULL if any of its input arguments is NULL (with the notable exception of a few functions that are specifically conceived for NULL handling). And that is a second similarity between NULL and a black hole: the gravity of a black hole pulls in everything that gets too close, causing it to “disappear” in the hole, and the rule of NULL propagation causes any expression that has a NULL in it to have no result, as if the other input values disappear as well.

Just to confuse matters more, default handling of NULL in a predicate is different. There is a valid reason for this, though. First of all, expressions are supposed to return a value that adheres to the rule of a datatype, but “Hey dude, I can’t answer that” is obviously not a valid value in any numeric, date, time, or datetime domain. It would of course be valid in a domain for character strings, but you can’t just overload what might already be a valid “regular” value with a special meaning. NULL however is valid, in any domain. So there really was no other choice but to return NULL if input data for an expression is missing. The reason that predicates can’t be handled the same way, is that a predicate is typically used in a WHERE expression, so there has to be a result for each row – even if some input data is missing, the DBMS still has to decide whether or not to include the row in the output. This problem was also solved by mimicking what humans would do. Suppose I’d ask you to tell me whether I am older than thirty-five. This time, I won’t restrict your answer to any domain, but I do force you to give an answer. And of course, I still refuse to disclose my date of birth. So, since you obviously can’t say “yes” or “no” without having a 50% chance of being wrong, what will your answer be? Probably some more or less polite variation of “bugger off, dude, how am I supposed to know that if you don’t disclose your birthday?” Translated back to database terms, you answer would not be true or false, but unknown. Not unknown because some input data is unknown (as some text books write), but unknown because some input data is missing.

This choice solves the problem, since we are now able to evaluate each predicate in a WHERE clause for each row, with the result being either true (row is included), false (row is omitted), or unknown (in which case the row is omitted as well). In a future post, I will cover how this affects logical expressions that involve AND, OR, or NOT to modify predicates, and how unknown is sometimes treated the same as false, yet other times treated the same as true, depending on the context of the predicate.