SQL NULL Operator Tutorial

Example Table

We will use the following table named employees for our examples:


    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(50),
        department VARCHAR(50),
        manager_id INT
    );

    INSERT INTO employees (employee_id, employee_name, department, manager_id) VALUES
    (1, 'Alice Johnson', 'HR', NULL),
    (2, 'Bob Smith', 'IT', 1),
    (3, 'Charlie Brown', 'Finance', NULL),
    (4, 'Diana Prince', 'IT', 2),
    (5, 'Eve Davis', 'Marketing', 3);

Employees Table

employee_id	employee_name	department	manager_id
1	Alice Johnson	HR	NULL
2	Bob Smith	IT	1
3	Charlie Brown	Finance	NULL
4	Diana Prince	IT	2
5	Eve Davis	Marketing	3

Using SQL NULL Operator

SQL NULL is used to represent missing or unknown data. The IS NULL and IS NOT NULL operators are used to test for NULL values.

Example: Find Employees with No Manager

To find employees who do not have a manager, use the following query:


    SELECT employee_name, department
    FROM employees
    WHERE manager_id IS NULL;

Result:

employee_name	Alice Johnson
department	HR
employee_name	Charlie Brown
department	Finance

Example: Find Employees with a Manager

To find employees who have a manager, use the following query:


    SELECT employee_name, department
    FROM employees
    WHERE manager_id IS NOT NULL;