On NULL

"Nothing will come of nothing."
Shakespeare, King Lear
Act I, Scene I, Line 90

"For we brought nothing into this world, and it is certain we can carry nothing out."
King James Bible
1 Timothy 6:7

What does it mean for something to be NULL?  Even better what is the meaning of meaning?  Even more interesting why does no one care?

These are interesting questions, but not really in the realm of a pragmatic programer.  How about an easier question, how does NULL work with different operations in different environments?  Now this is a question that we can search out an answer for and maybe learn something along the way.

NULL in SQL Server 2012

Databases and NULLs, nothing is more hotly debated (well maybe NoSQL v. RDBS, but I digress).  Lets look at how NULL works with subtraction, addition, multiplication, and division in SQL Server 2012.

SELECT '9 - NULL' AS Problem, 9 - NULL AS 'Answer'

UNION ALL

SELECT 'NULL - 9' AS Problem, NULL - 9 AS 'Answer'

UNION ALL

SELECT 'NULL - NULL' AS Problem, NULL - NULL AS 'Answer'

UNION ALL

SELECT '9 + NULL' AS Problem, 9 + NULL AS 'Answer'

UNION ALL

SELECT 'NULL + 9' AS Problem, NULL + 9 AS 'Answer'

UNION ALL

SELECT 'NULL + NULL' AS Problem, NULL + NULL AS 'Answer'

UNION ALL

SELECT '9 * NULL' AS Problem, 9 * NULL AS 'Answer'

UNION ALL

SELECT 'NULL * 9' AS Problem, NULL * 9 AS 'Answer'

UNION ALL

SELECT 'NULL * NULL' AS Problem, NULL * NULL AS 'Answer'

UNION ALL

SELECT '9 / NULL' AS Problem, 9 / NULL AS 'Answer'

UNION ALL

SELECT 'NULL / 9' AS Problem, NULL / 9 AS 'Answer'

UNION ALL

SELECT 'NULL / NULL' AS Problem, NULL / NULL AS 'Answer'

Gives us the following on SQLFiddle.

http://sqlfiddle.com/#!6/6dc1f/2

All NULL, interesting.

NULL in MySQL 5.6.6 m9

How about MySQL?  (I do not use MySQL, so I just picked the newest version on SQLFiddle)

After a quick check of the MySQL documentation we see that we can use the exact same syntax to SELECT from no table (FROM DUAL can also be used).

SELECT '9 - NULL' AS Problem, 9 - NULL AS 'Answer'

UNION ALL

SELECT 'NULL - 9' AS Problem, NULL - 9 AS 'Answer'

UNION ALL

SELECT 'NULL - NULL' AS Problem, NULL - NULL AS 'Answer'

UNION ALL

SELECT '9 + NULL' AS Problem, 9 + NULL AS 'Answer'

UNION ALL

SELECT 'NULL + 9' AS Problem, NULL + 9 AS 'Answer'

UNION ALL

SELECT 'NULL + NULL' AS Problem, NULL + NULL AS 'Answer'

UNION ALL

SELECT '9 * NULL' AS Problem, 9 * NULL AS 'Answer'

UNION ALL

SELECT 'NULL * 9' AS Problem, NULL * 9 AS 'Answer'

UNION ALL

SELECT 'NULL * NULL' AS Problem, NULL * NULL AS 'Answer'

UNION ALL

SELECT '9 / NULL' AS Problem, 9 / NULL AS 'Answer'

UNION ALL

SELECT 'NULL / 9' AS Problem, NULL / 9 AS 'Answer'

UNION ALL

SELECT 'NULL / NULL' AS Problem, NULL / NULL AS 'Answer'

Let's see what we get.

http://sqlfiddle.com/#!9/6dc1f/1

Results are exactly the same as SQL Server, everything is NULL.

NULL in SQLite (SQL.js)

How about using NULL in something a bit different, how about running it in SQL.js?

I've never really used SQLite, but a quick look at StackOverflow says that we can continue with our example of SELECT with no table.

SELECT '9 - NULL' AS Problem, 9 - NULL AS 'Answer'

UNION ALL

SELECT 'NULL - 9' AS Problem, NULL - 9 AS 'Answer'

UNION ALL

SELECT 'NULL - NULL' AS Problem, NULL - NULL AS 'Answer'

UNION ALL

SELECT '9 + NULL' AS Problem, 9 + NULL AS 'Answer'

UNION ALL

SELECT 'NULL + 9' AS Problem, NULL + 9 AS 'Answer'

UNION ALL

SELECT 'NULL + NULL' AS Problem, NULL + NULL AS 'Answer'

UNION ALL

SELECT '9 * NULL' AS Problem, 9 * NULL AS 'Answer'

UNION ALL

SELECT 'NULL * 9' AS Problem, NULL * 9 AS 'Answer'

UNION ALL

SELECT 'NULL * NULL' AS Problem, NULL * NULL AS 'Answer'

UNION ALL

SELECT '9 / NULL' AS Problem, 9 / NULL AS 'Answer'

UNION ALL

SELECT 'NULL / 9' AS Problem, NULL / 9 AS 'Answer'

UNION ALL

SELECT 'NULL / NULL' AS Problem, NULL / NULL AS 'Answer'

http://sqlfiddle.com/#!5/6dc1f/1

Interesting, we get nothing, no text, no NULL, nothing.  Hmmm, what does this mean?  How about another test.

SELECT NULL AS 'value'

UNION ALL

SELECT '' AS 'value'

UNION ALL

SELECT 9 AS 'value'
;

INSERT INTO notNeeded (id)
VALUES(NULL),(8);

SELECT * FROM notNeeded; -- Needed now!

Hmmm, we get the following from SQLFiddle now.

http://sqlfiddle.com/#!5/6dc1f/7

A quick Google search and we find the following blog post which states.

"The .nullvalue command tells the SQLite to show NULL values as NULL. SQLite shows empty strings for NULL values by default"
-- Jan Bodnar, Inserting, updating and deleting data

As I was thinking, so the results are the same as SQL Server and MySQL, just NULL all the way down.

NULL in F#

How about a strongly typed language like F#?

We get the following:

9 - null;;
gives us
  9 - null;;
  --^

stdin(5,3): error FS0043: The type 'int' does not have 'null' as a proper value

Hmmm, how about this:

null - null;;
gives us
  null - null;;
  -----^

stdin(3,6): error FS0071: Type constraint mismatch when applying the default type 'int' for a type inference variable. The type 'int' does not have 'null' as a proper value Consider adding further type constraints

No luck, it will not even compile.  Looks like strong typing is working.

NULL in JavaScript

How about JavaScript (using node.js 0.10)?

C:\Documents and Settings\Mike Harris>node
> 9 - null
9
> null - 9
-9
> null - null
0
>

JavaScript is built to not "fail" and as such it treats null the same as 0.  Hmmm, how about if we divide by null?

> 9 / null
Infinity
> null / 9
0
> null / null
NaN
>

Interesting.  Let's look at the other odd values in JavaScript like [], {}, and NaN.

> 9 - []
9
> [] - 9
-9
> [] - []
0
> 9 - {}
NaN
> {} - 9
NaN
> {} - {}
NaN
> NaN - 9
NaN
> 9 - NaN
NaN
> NaN - NaN
NaN
>

Hmmm, [] is like 0 while {} and NaN are the same!  Interesting.

How about if we mix them?

> [] - {}
NaN
> {} - []
NaN
> NaN - {}
NaN
> NaN - []
NaN
> {} - NaN
NaN
> [] - NaN
NaN
> [] / {}
NaN
>

Gary Bernhardt did something very similar (but a lot better and funnier) at CodeMash 2012.

"With that harsh, noble, simple nothing"

-- Shakespeare, Cymbeline
Act III, Scene IV, Line 134