What is the Data Type of NULL?

NullJust 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!

Image

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

So, is it always true that “Null is un-typed“? Apparently not.

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.