NULL check operators in ClickHouse: IS NULL and IS NOT NULL with Examples

  

In ClickHouse, NULL represents missing or unknown data. It is used in columns that are defined with Nullable types (e.g., Nullable(Int32)). NULL is not equal to any value, not even to another NULL. You must explicitly check for NULL using IS NULL or IS NOT NULL.

 

Following table summarizes the same.

 

Operator	Description
value IS NULL	Returns true if the value is NULL
value IS NOT NULL	Returns true if the value is not NULL

 

-- IS NULL
SELECT NULL IS NULL;            -- returns 1
SELECT 'Alice' IS NULL;         -- returns 0

-- IS NOT NULL
SELECT NULL IS NOT NULL;        -- returns 0
SELECT 'Alice' IS NOT NULL;     -- returns 1

Let’s work with these operators with a real time example.

 

Step 1: Create a database demo_db.

CREATE DATABASE IF NOT EXISTS demo_db;

   

Step 2: Create users table and insert some data into it.

 

CREATE TABLE demo_db.users (
  id UInt32,
  name Nullable(String)
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO demo_db.users VALUES (1, 'Alice'), (2, NULL), (3, 'Bob');

krishna :) SELECT * FROM demo_db.users;

SELECT *
FROM demo_db.users

Query id: a80ac806-6f69-4297-a1c5-b68316bf175c

   ┌─id─┬─name──┐
1. │  1 │ Alice │
2. │  2 │ ᴺᵁᴸᴸ  │
3. │  3 │ Bob   │
   └────┴───────┘

3 rows in set. Elapsed: 0.004 sec.

   

Step 3: Querying with IS NULL

 

SELECT * FROM demo_db.users WHERE name IS NULL;

krishna :) SELECT * FROM demo_db.users WHERE name IS NULL;

SELECT *
FROM demo_db.users
WHERE name IS NULL

Query id: 854ac3ff-ff38-4f1b-bd4a-911edea327a3

   ┌─id─┬─name─┐
1. │  2 │ ᴺᵁᴸᴸ │
   └────┴──────┘

1 row in set. Elapsed: 0.008 sec.

   

Step 4: Querying with IS NOT NULL.

 

SELECT * FROM demo_db.users WHERE name IS NOT NULL;

krishna :) SELECT * FROM demo_db.users WHERE name IS NOT NULL;

SELECT *
FROM demo_db.users
WHERE name IS NOT NULL

Query id: 60b749eb-ce05-47b8-b192-12f9720b8159

   ┌─id─┬─name──┐
1. │  1 │ Alice │
2. │  3 │ Bob   │
   └────┴───────┘

2 rows in set. Elapsed: 0.005 sec.

 

 

Previous                                                    Next                                                    Home