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.
SQL IS NULL
Let’s have a quick look at SQL is null operator syntax.
|
1 2 3 |
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.
|
1 2 3 |
SELECT StudentPercent FROM Student WHERE StudentAge IS NULL; |
Output:
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.
|
1 2 3 |
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:
|
1 2 3 |
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 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.
|
1 2 3 |
SELECT ProductName FROM Supplier WHERE SupplierName IS NOT NULL; |
Output: