PostgreSQL: IS NULL and IS NOT NULL

In PostgreSQL, a NULL means missing data. The PostgreSQL table can be created with a column defined as nullable. It's optional to populate data in such columns while inserting a row in the table. In that case, that column will be empty or will have NULL values. You can insert or update data to this column later on whenever you want.

NULL is not a value itself, so you cannot compare it with any other values like numbers or strings using any comparison operators, e.g. =, !=, <, >. The special clause IS NULL or IS NOT NULL is needed to check NULL value exists or not in a table.

Let's use the following Employee table to demonstrate IS NULL and IS NOT NULL operators:

In the above table, the email column includes NULL values. The following query fetches employees whose email is NULL using IS NULL operator.

Example: IS NULL

SELECT * FROM employee WHERE email IS NULL;

Use IS NOT NULL to fetch employees with email.

Example: IS NOT NULL

SELECT * FROM employee WHERE email IS NOT NULL;