SQL IS NOT NULL 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),
        salary DECIMAL(10, 2)
    );

    INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
    (1, 'John Doe', 'HR', 50000.00),
    (2, 'Jane Smith', 'Finance', 60000.00),
    (3, 'Emily Davis', 'IT', NULL),
    (4, 'Michael Brown', NULL, 80000.00),
    (5, 'Jessica Wilson', 'IT', 90000.00);

Employees Table

employee_id	employee_name	department	salary
1	John Doe	HR	50000.00
2	Jane Smith	Finance	60000.00
3	Emily Davis	IT	NULL
4	Michael Brown	NULL	80000.00
5	Jessica Wilson	IT	90000.00

Using SQL IS NOT NULL

The SQL IS NOT NULL operator is used to test for non-empty values (i.e., values that are not NULL) in a column.

Example 1: Select Employees with Non-NULL Salaries

To select employees who have a salary that is not NULL, use the following query:


    SELECT employee_name, salary
    FROM employees
    WHERE salary IS NOT NULL;

Result:

employee_name	John Doe
salary	50000.00
	Jane Smith
	60000.00
	Michael Brown
	80000.00
	Jessica Wilson
	90000.00

Example 2: Select Employees with Non-NULL Departments

To select employees who have a department that is not NULL, use the following query:


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