Summary -

In this topic, we described about the Select Null with detailed example.

NULL in a table represents that the field has no value. NULL is different from zero and space. While inserting and updating the rows, there might be an optional column in table. If the optional column value not specified in the INSERT statement, the column gets updated with NULL value by default.

To verify the column value is NULL or NOT, we can use "IS NULL" or "IS NOT NULL". We should make a note that the NULL value can't compare with operators like =, >, <.

Syntax of IS NULL –

SELECT column_names
FROM table_name
WHERE column_name IS NULL; 

The above query fetches the rows that column_name having NULL values.


Syntax of IS NOT NULL –

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL; 

The above query fetches the rows that column_name not having 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 which are having NULL values.

Requirement - Fetch employee details who has no manager assigned from employee_details table. 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 – Fetch rows which are not having NULL values.

Requirement - Fetch employee details who has a manager assigned from employee_details table. 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 rows which are having NULL values in a column.

Requirement – Update manager details for the employee with id 001 in employee_details table. 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, the corresponding row gets updated with manager0 as manager_id like below -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director Employee0 2019-07-11 45000.00 1000

Scenario – Deleting the rows which are having NULL values in a column.

Requirement – delete employee who are having manager_id as NULL. The query was as follows -

 DELETE FROM employee_details WHERE manager_id IS NULL; 

By executing above query, rows having manager_id gets deleted and the result table as shown below -

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