What is the Data Type of NULL?

April 18, 2013 0 Comments

Just for fun, let’s try and figure out what is the data type of NULL!

NULL is a marker that represents missing, unknown, or inapplicable data. NULL is un-typed in SQL, meaning that it is not designated as a NUMBER, CHAR, or any other specific data type.

Is it always true that “Null is un-typed“? Let’s find out!

Using the CTAS (CREATE TABLE AS SELECT) examples below, we can observe how several popular databases handle a NULL.

In Vertica the CTAS in Vertica reveals that a NULL is treated as a data type of VARCHAR having a length of 1 byte.

dbadmin=> CREATE TABLE null_test (c1) AS SELECT null;

CREATE TABLE

dbadmin=> \d null_test;

List of Fields by Tables

Schema    |   Table   | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key

--------------+-----------+--------+------------+------+---------+----------+-------------+-------------

vertica01_wh | null_test | c1     | varchar(1) |    1 |         | f        | f           |

(1 row)

So what is the data type of NULL and MySQL, SQL Server and Oracle?

MySQL:

In MySQL we see that a NULL will default to a data type of BINARY with a length of 0. Note that the BINARY data type in MySQL is similar to the CHAR in other databases.

MySQL> CREATE TABLE k AS SELECT null;

Query OK, 1 row affected (0.10 sec)

Records: 1  Duplicates: 0  Warnings: 0

MySQL> desc k;

+-------+-----------+------+-----+---------+-------+

| Field | Type      | Null | Key | Default | Extra |

+-------+-----------+------+-----+---------+-------+

| NULL  | binary(0) | YES  |     | NULL    |       |

+-------+-----------+------+-----+---------+-------+

1 row in set (0.00 sec)

SQL Server:

A CTAS in SQL Server on the other hand will express a NULL with a data type of INT having a length of 4 bytes! Crazy!

Oracle:

Oracle is a little more finicky in that it will not allow us to run a CTAS statement using a NULL value data source for a column:

SQL> CREATE TABLE k AS SELECT null k1 FROM dual;

CREATE TABLE k AS SELECT null k1 FROM dual

.                        *

ERROR at line 1:

ORA-01723: zero-length columns are not allowed

But we can create a view (CVAS) using the same logic. Doing so shows that Oracle treats a NULL with a default data type of VARCHAR2 having a length of zero:


SQL> CREATE VIEW k AS SELECT null k1 FROM dual;

View created.

SQL> SELECT data_type, data_length

. 2    FROM user_tab_columns

. 3   WHERE table_name = 'K';

DATA_TYPE   DATA_LENGTH

---------   -----------

VARCHAR2    0