Null

From LXF Wiki

Null is a word that pops up quite a lot in the world of computers and Linux in particular. It usually refers to something that is either empty, has no value or has an unknown value.

/dev/null

For example, in Linux there is a device called null (i.e. /dev/null). Anything you write to this device simply disappears.... What's the point of that? Occasionaly you may wish to redirect output from a command to the null device so it doesn't show up on the screen, or, in the case of a cron job, so it doesn't get emailed to the root user.

Database NULL entry

'NULL' appears in databases too, where it has a slightly different meaning than it does in many programming languages. This is often the cause of many headaches, hence the classic SQL query in the MySQL manual:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
|-
| 1 = NULL || 1 <> NULL || 1 < NULL || 1 > NULL ||
+----------+-----------+----------+----------+
|-
|     NULL ||      NULL ||     NULL ||     NULL ||
+----------+-----------+----------+----------+

As you can see, '1 = NULL' is NULL, '1 <> NULL' is NULL, '1 < NULL' is NULL and '1 > NULL' is NULL - this gives many programmers headaches because the are used programming languages where:

  1. null is synonymous with empty (or even zero)
  2. these comparators are boolean operators (i.e. they can only give one of two results, true or false) and could therefore never return null.
  3. null is a value

However, in SQL things are different

  1. null is synonymous with unknown (or not applicable) in SQL
  2. these comparators use higher-valued logic whereby they may return one of three results, i.e. true, false and null (unknown)
  3. null is the absence of a value

One of the things which causes the most headaches, which the MySQL manual doesn't seem to cover, is that all comparisons with null result in null, even 'NULL NULL' and 'NULL ==== NULL':

mysql> select NULL = NULL;
+-------------+
|-
| NULL = NULL ||
+-------------+
|-
|        NULL ||
+-------------+

In programming languages where null is synonymous with empty, then 'null null' would be true (i.e. 'empty empty' is true). In SQL however, because null means unknown, the result of any comparison with null is null, i.e. 'unknown = unknown' is unknown.

SQL provides the 'IS NULL' and 'IS NOT NULL' operator to allow checking whether an attribute is null (or not). Because the value of an attribute is either known or not, these operators will only ever return a boolean value (i.e. true or false).