SQL Tutorial for Beginners: SQL IS NULL and IS NOT NULL

Learn about SQL IS NULL and IS NOT NULL with the help of examples. Learn how to use the SQL IS NULL and IS NOT NULL operators to filter data based on whether or not a column contains a value.

In SQL, IS NULL and IS NOT NULL are used to check if a column in a table contains a NULL value or not.

IS NULL Syntax

In SQL, the IS NULL condition is used to select rows if the specified field is NULL. It has the following syntax:

SELECT column1, column2, ...
FROM table
WHERE column_name IS NULL;

Here,

• column1, column2, ... are the table columns
• table is the table name from where we select the data
• column_name is the name of the column you want to check for NULL

For example,

-- select rows with NULL email values

SELECT *
FROM Employee
WHERE email IS NULL;

Here, the above SQL query retrieves all the rows from the Employee table where the value of the email column is NULL.

Example: IS NULL in SQL

Note: Empty values are considered NULL. However, space and 0 are not considered NULL.

IS NOT NULL

In SQL, the IS NOT NULL condition is used to select rows if the specified field is NOT NULL. It has the following syntax:

SELECT column1, column2, ...
FROM table
WHERE column_name IS NOT NULL;

Here,

• column1, column2, ... are the table columns
• table is the table name from where we select the data
• column_name is the name of the column you want to check for NOT NULL

For example,

-- select rows where email is not NULL

SELECT *
FROM Employee
WHERE email IS NOT NULL;

Here, the above SQL query retrieves all the rows from the Employee table where the value of the email column is NOT NULL.

Example: IS NOT NULL in SQL

IS NULL With COUNT()

We can use the COUNT() function with IS NULL to count the number of rows with an empty field. For example,

SELECT COUNT(*)
FROM Employee
WHERE email IS NULL;

Here, the SQL query retrieves the count of all the rows from the Employee table where the value of the email column is NULL.

Example: IS NULL with COUNT() in SQL

Similarly, we can use the COUNT() function with IS NOT NULL to count the number of non-empty fields.

#sql