- Categories:
IS [ NOT ] NULL¶
Determines whether an expression is NULL or is not NULL.
Syntax¶
<expr> IS [ NOT ] NULL
Returns¶
Returns BOOLEAN true or false.
Examples¶
Create and load the table:
CREATE OR REPLACE TABLE i (id NUMBER, col1 NUMBER, col2 NUMBER); INSERT INTO i (id, col1, col2) VALUES (1, 0, 5), (2, 0, null), (3, null, 5), (4, null, null);
Show the data:
SELECT * FROM i ORDER BY id; +----+------+------+ | ID | COL1 | COL2 | |----+------+------| | 1 | 0 | 5 | | 2 | 0 | NULL | | 3 | NULL | 5 | | 4 | NULL | NULL | +----+------+------+
Use IS NOT NULL
:
SELECT * FROM i WHERE col1 IS NOT NULL ORDER BY id; +----+------+------+ | ID | COL1 | COL2 | |----+------+------| | 1 | 0 | 5 | | 2 | 0 | NULL | +----+------+------+
Use IS NULL
:
SELECT * FROM i WHERE col2 IS NULL ORDER BY id; +----+------+------+ | ID | COL1 | COL2 | |----+------+------| | 2 | 0 | NULL | | 4 | NULL | NULL | +----+------+------+
Use a combination of IS NOT NULL
and IS NULL
:
SELECT * FROM i WHERE col1 IS NOT NULL OR col2 IS NULL ORDER BY id; +----+------+------+ | ID | COL1 | COL2 | |----+------+------| | 1 | 0 | 5 | | 2 | 0 | NULL | | 4 | NULL | NULL | +----+------+------+