SQL IS NULL and IS NOT NULL

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.

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.

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.

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