Summary -

In this topic, we described about the IS NULL Condition with detailed example.

IS NULL condition checks NULL values in the table. IS NULL condition is used to fetch matching rows or columns which contain NULL values in the specified condition. IS NULL condition is used in SELECT, UPDATE, DELETE statements.

SELECT Syntax –

SELECT column1, column2, …, columnN
 FROM table_name
 WHERE column_name IS NULL; 

UPDATE Syntax –

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN
WHERE column_name IS NULL; 

DELETE Syntax

DELETE FROM table_name 
WHERE column_name IS NULL; 

  • column1, column2, …, columnN - Specifies the column names from table.
  • table_name – Specifies the name of the table.
  • column_name - Specifies the column that used to check for NULL values.

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch rows or values having NULL.

Requirement – Fetch all details of employees whose manager_id is NULL. The query was as follows –

SELECT * FROM employee_details WHERE manager_id IS NULL; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000

Scenario – Update the rows that are having NULL values.

Requirement – Update the manager as "Employee0" whose manager is having NULL and having employee id is 001. The query was as follows –

UPDATE employee_details
SET manager_id = "Employee0" 
WHERE manager_id IS NULL AND emp_id = 001; 

By executing above query, we can update the NULL value with "Employee0" in employee_details table whose emp_id is 001. The table after updation was as follows-

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director Employee0 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Delete rows which contain NULL value.

Requirement – Delete employees whose manager is empty. The query was as follows –

DELETE FROM employee_details WHERE manager_id IS NULL; 

By executing above query, we can delete rows which contain null values. The updated table was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director Employee0 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000