Summary -

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

IS NOT NULL condition checks for NOT NULL values. IS NOT NULL condition is used to fetch rows or columns which are having valid values. IS NOT NULL condition is used in SELECT, UPDATE, DELETE statements.

SELECT Syntax –

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

UPDATE Syntax –

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

DELETE Syntax –

DELETE FROM table_name
 WHERE column_name IS NOT 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 not 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 using IS NOT NULL Condition.

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

SELECT * FROM employee_details WHERE manager_id IS NOT 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
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 – Update the rows using IS NOT NULL condition.

Requirement - Update the salary as 18000 for the employee who have valid manager updated and designation is "Clerk". The query was as follows –

UPDATE employee_details SET salary = 18000 
 WHERE manager_id IS NOT NULL AND designation= "Clerk"; 

By executing above query, we can update the salary for the employee who have valid manager updated and designation is "Clerk". The updated table was as follows-

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 18000.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 using IS NOT NULL condition.

Requirement – Delete employee details who has manager updated. The query was as follows –

DELETE FROM employee_details 
WHERE emp_id = 009 AND manager_id IS NOT NULL; 

By executing above query, we can delete rows who has valid manager. The output was as follows -

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