Sometimes we have columns where null values are allowed. In such cases, SQL IS NULL is a very useful operator.

SQL IS NULL operator is used to checking if the value of a column is null or not. The operator filters the result set based on the null column value.

Let’s discuss in detail about the IS NULL operator.


Let’s have a quick look at SQL is null operator syntax.

Above query will give us result set where specified column value is null.

We will understand the above-mentioned syntax in more detail through some examples.

Let’s consider the following Student table for example purpose.

RollNo StudentName StudentGender StudentAge StudentPercent
1 George M 14 85
2 Monica F 12 88
3 Jessica F null 84
4 Tom M null 78

Scenario: Get the percentage of students whose age is null.


Oracle Empty String is equivalent to NULL

Null is not just limited to the null keyword in Oracle database, in fact, the columns who have blank value also are considered as null when using IS NULL operator. Note that these columns types should be VARCHAR and not CHAR.

We found similar behavior for PostgreSQL database but not in MySQL database. So this seems to be database specific behaviour.

Let’s consider the following Supplier table for example purpose.

ProductId ProductName SupplierName
1 Cookie ABC
2 Cheese
3 Chocolate
4 Jam XDE

Scenario: Get the name of the product where supplier name is not available.




SQL IS NOT NULL operator is used to filter the result if the column that is used in WHERE clause contains non-null values.

Let’s discuss in detail about IS NOT NULL operator.


In the syntax above the column values that are not null will be filtered for the result.

Let’s consider the earlier defined Supplier table for example purpose.

Scenario: Get the name of the product whose supplier name is not null.


By admin

Leave a Reply

%d bloggers like this: