Sunny Books
What we have

Replace NULL Values in Query Results

There are three ways to replace NULL values in SQL query result set: ISNULL function, COALESCE function, and CASE statement.

Let's first query the employee table to get all employees and their manager :

The employee table:

employee_id name gender salary department_id manager_id
1 David male 7000 1 9
2 Alice female 7000 2 1
3 Rose female 7500 2 10
4 Jack male 7600 3 1
5 Tom male 8000 1 1
6 Alexa female 7300 3 1
7 Chantal female 7600 1 3
8 Ella female 7200 3 3
9 Kaven male 8000 NULL NULL
10 Erik male 7800 NULL NULL

SQL query with NULL values

SELECT e.employee_id, e.name,  m.name as manager
FROM [em].[employee] e
  LEFT JOIN [em].[employee] m ON e.manager_id=m.employee_id

and the result:

employee_id name manager
1 David Kaven
2 Alice David
3 Rose Erik
4 Jack David
5 Tom David
6 Alexa David
7 Chantal Rose
8 Ella Rose
9 Kaven NULL
10 Erik NULL

Replace NULL values

use ISNULL function

SELECT e.employee_id, e.name, ISNULL(m.name, 'no manager') as manager
FROM [em].[employee] e 
  LEFT JOIN [em].[employee] m	ON e.manager_id=m.employee_id

use COALESCE function

/* COALESCE returns the first non null value*/
SELECT e.employee_id, e.name, COALESCE(m.name, 'no manager') as manager
FROM [em].[employee] e
  LEFT JOIN [em].[employee] m	ON e.manager_id=m.employee_id

use CASE statement

SELECT e.employee_id, e.name,
  CASE WHEN m.name IS NULL THEN 'no manager' ELSE m.name END as manager
FROM [em].[employee] e 
LEFT JOIN [em].[employee] m ON e.manager_id=m.employee_id

All the three methods get the same result:

employee_id name manager
1 David Kaven
2 Alice David
3 Rose Erik
4 Jack David
5 Tom David
6 Alexa David
7 Chantal Rose
8 Ella Rose
9 Kaven no manager
10 Erik no manager
SUNWEB EXPERT