Syntax¶

<expr>IS[NOT]NULL

Returns¶

Returns a BOOLEAN.

• When IS NULL is specified, the value is TRUE if the expression is NULL. Otherwise, returns FALSE.

• When IS NOT NULL is specified, the value is TRUE if the expression is not NULL. Otherwise, returns FALSE.

Examples¶

Create thetest_is_not_null table and load the data:

CREATEORREPLACETABLEtest_is_not_null(idNUMBER,col1NUMBER,col2NUMBER);INSERTINTOtest_is_not_null(id,col1,col2)VALUES(1,0,5),(2,0,NULL),(3,NULL,5),(4,NULL,NULL);

Show the data in thetest_is_not_null table:

SELECT*FROMtest_is_not_nullORDERBYid;

+----+------+------+| ID | COL1 | COL2 ||----+------+------||  1 |    0 |    5 ||  2 |    0 | NULL ||  3 | NULL |    5 ||  4 | NULL | NULL |+----+------+------+

Use IS NOT NULL to return the rows for which the values incol1 are not NULL:

SELECT*FROMtest_is_not_nullWHEREcol1ISNOTNULLORDERBYid;

+----+------+------+| ID | COL1 | COL2 ||----+------+------||  1 |    0 |    5 ||  2 |    0 | NULL |+----+------+------+

Use IS NULL to return the rows for which the values incol2 are NULL:

SELECT*FROMtest_is_not_nullWHEREcol2ISNULLORDERBYid;

+----+------+------+| ID | COL1 | COL2 ||----+------+------||  2 |    0 | NULL ||  4 | NULL | NULL |+----+------+------+

Use a combination of IS NOT NULL and IS NULL to return the rows for whicheither ofthe following conditions is met:

• The values incol1 are not NULL.

• The values incol2 are NULL.

SELECT*FROMtest_is_not_nullWHEREcol1ISNOTNULLORcol2ISNULLORDERBYid;

+----+------+------+| ID | COL1 | COL2 ||----+------+------||  1 |    0 |    5 ||  2 |    0 | NULL ||  4 | NULL | NULL |+----+------+------+

Use a combination of IS NOT NULL and IS NULL to return the rows for whichboth ofthe following conditions are met:

• The values incol1 are not NULL.

• The values incol2 are NULL.

SELECT*FROMtest_is_not_nullWHEREcol1ISNOTNULLANDcol2ISNULLORDERBYid;

+----+------+------+| ID | COL1 | COL2 ||----+------+------||  2 |    0 | NULL |+----+------+------+