What is the Data Type of NULL?
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.
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.
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:
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:
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.
No comments yet... Be the first to leave a reply!