SQL IS NULL and IS NOT NULL Operators: How to Check for Null Values

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;

Run Code

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;

Run Code

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;

Run Code

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.

• This blog post was originally published at:https://www.programiz.com/

#sql  #database