What is the Data Type of NULL?

[JimKnicely]

Just for fun ...

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.


Vertica:

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

Code: Select all

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
--------------+-----------+--------+------------+------+---------+----------+-------------+-------------
 intersect_wh | null_test | c1     | varchar(1) |    1 |         | f        | f           |
(1 row)

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.

Code: Select all

(mydbadmin@localhost) [jim]> create table k as select null;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

(mydbadmin@localhost) [jim]> 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:

Code: Select all

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:

Code: Select all

SQL> create view k as select null k1 from dual;

View created.

SQL> select data_type, data_length from user_tab_columns where table_name = 'K';

DATA_TYPE   DATA_LENGTH
---------   -----------
VARCHAR2    0

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