SQL IS NULL | Check Field With No Value

IS NULL is an operator in SQL which can check for fields having no value (or empty value). You cannot use the comparison operators (=, !=) to check for null values. Instead, you need to use IS NULL or IS NOT NULL operator for checking fields which have no value.

Syntax

IS NULL Syntax in SQL

SELECT * FROM table WHERE column1 IS NULL;

You can use the operator along with SQL WHERE Condition. It returns the rows which have the value of column1 as null.

IS NOT NULL Syntax in SQL

Similarly, you can use the following query to fetch rows with column1 value as not null.

SELECT * FROM table WHERE column1 IS NOT NULL;

Demo Table

For instance, consider the demo table to refer for the all the queries in this article.

+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | NULL  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | NULL  |
+----+--------+--------+------------+-------+

Notice, the third and the fifth row have NULL values in the column ‘team’.

Examples

SQL IS NULL Example

The IS NULL operator checks for fields with empty values. Since, we cannot use Comparison Operators (like =, !=, < or > etc), let’s use the operator to fetch rows with team as null.

SELECT * FROM employees WHERE team is NULL;

The above SELECT Query will fetch only those rows which have empty team.

+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  3 | John   |  15000 |          3 | NULL  |
|  5 | Ross   |  30000 |          8 | NULL  |
+----+--------+--------+------------+-------+

SQL IS NOT NULL Example

Similarly, we can fetch the rows which do not have an empty value in the field team by using the IS NOT NULL Operator.

SELECT * FROM employees WHERE team is NOT NULL;

+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
+----+--------+--------+------------+-------+

Additionally, you can also learn about using the SQL WHERE Clause which is used with the operators.

Conclusion

In this article, we discussed the SQL IS NULL and IS NOT NULL Operators. You can learn more about them on Wikipedia. Additionally, you can also learn about SQL Queries on Concatly.