SQL is null, SQL is not null

Filed Under: SQL
SQL IS NULL, SQL IS NOT NULL

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 check if the value of a column is null or not. The operator filters the result set based on null column value.

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

SQL IS NULL

SQL IS NULL, SQL IS NOT NULL
Let’s have a quick look at SQL is null operator syntax.


SELECT Column(s) FROM table_name WHERE column IS NULL;

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.


SELECT StudentPercent FROM Student WHERE StudentAge IS NULL;

Output:

StudentPercent
88
78

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 product where supplier name is not available.


SELECT ProductName FROM Supplier WHERE SupplierName IS NULL;

Output:

ProductName
Cheese
Chocolate

SQL IS NOT NULL

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.

Syntax:


SELECT Column(s) FROM table_name WHERE Column IS NOT NULL;

In the syntax above the column values that are not null will be filtered for 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.


SELECT ProductName FROM Supplier WHERE SupplierName IS NOT NULL;

Output:

SupplierName
Cookie
Jam

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages