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)
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)
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
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