In SQL Server:

  • NULL represents missing, unknown, or undefined data.
  • NULL is not the same as 0 (numeric zero) or an empty string ”.

Important: You cannot use = to compare with NULL.

Step 1: Checking for NULL values

Use IS NULL or IS NOT NULL to filter NULL values.

Example table: Employees

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown NULL 6000
4 Sara White HR 4700
5 Tom Green NULL 5200

Find rows where Department is NULL

SELECT EmployeeID, FirstName, Department
FROM Employees
WHERE Department IS NULL;

Result:

EmployeeID FirstName Department
3 Mike NULL
5 Tom NULL

Find rows where Department is NOT NULL

SELECT EmployeeID, FirstName, Department
FROM Employees
WHERE Department IS NOT NULL;

Result:

EmployeeID FirstName Department
1 John IT
2 Jane HR
4 Sara HR

Step 2: NULL in expressions

  • Any arithmetic operation with NULL results in NULL.
SELECT Salary, Salary + 1000 AS NewSalary
FROM Employees;
Salary NewSalary
5000 6000
4500 5500
6000 7000
4700 5700
5200 6200

If Salary were NULL, Salary + 1000 would also be NULL.

Step 3: Using ISNULL() to replace NULL

ISNULL(expression, replacement) replaces NULL with a specified value.

SELECT FirstName, ISNULL(Department, 'Not Assigned') AS Dept
FROM Employees;

Result:

FirstName Dept
John IT
Jane HR
Mike Not Assigned
Sara HR
Tom Not Assigned

Step 4: NULL in logical comparisons

  • NULL is unknown, so Salary = NULL will never be true.
  • Always use IS NULL or IS NOT NULL for comparisons.

Incorrect:

SELECT * FROM Employees WHERE Department = NULL;

Returns no rows.

Correct:

SELECT * FROM Employees WHERE Department IS NULL;

Step 5: NULL in ORDER BY

  • In SQL Server, NULL values appear first in ascending order and last in descending order.
SELECT FirstName, Department
FROM Employees
ORDER BY Department ASC;

Result:

FirstName Department
Mike NULL
Tom NULL
John IT
Jane HR
Sara HR

Categorized in:

SQL Server,