Hive: Difference between NULL col_name and NULL as col_name in table DDL

Question

Can someone please explain me the difference between "NULL col_name" and "NULL as col_name" in hive table DDL. Examples below:

CREATE TABLE table_null AS SELECT c1, c2, c3, c4 FROM( 'col1' as c1, 'col2' as c2, NULL as c3, NULL c4 ......

Answer 1

In Hive and MySQL, the select statement having <value/col_name> as <col_alias> and <value/col_name> <col_alias> gives the same output. as is optional keyword.

Below examples may clarify better:

• Hive query

• MySQL query

In other databases also it may follow the same rule.

Answer 2

Both are the same. They can be tested this way:

select *
from (
    select NULL col1, NULL as col2
    from test_nulls ) tabl
where col1 is NULL and col2 is NULL;