NULL behavior in comparisons in SQL

Introduction

NULL means we don't know the value. When we compare NULL with anything, the result is also unknown, so it behaves differently than normal values.

When checking if a database field has missing or unknown data.

When filtering rows that might have NULL values in a column.

When writing conditions that need to handle unknown or missing information.

When you want to find rows where a value is definitely missing.

When you want to avoid wrong results caused by comparing NULLs directly.

Syntax

SQL

expression IS NULL
expression IS NOT NULL

Use IS NULL or IS NOT NULL to check for NULL values because normal comparison operators (=, <>) do not work with NULL.

Comparisons like 'column = NULL' always return unknown, so they don't find NULLs.

Examples

Finds all employees who do not have a manager assigned (manager_id is unknown).

SQL

SELECT * FROM employees WHERE manager_id IS NULL;

Finds all orders that have a known delivery date.

SQL

SELECT * FROM orders WHERE delivery_date IS NOT NULL;

This returns no rows because comparing with NULL using '=' does not work.

SQL

SELECT * FROM products WHERE price = NULL;

Sample Program

This creates a table with some values and NULL. It shows how IS NULL returns true for NULL values, but '=' comparison with NULL returns unknown (NULL).

SQL

CREATE TABLE test_null (
  id INT,
  value INT
);

INSERT INTO test_null VALUES (1, 10), (2, NULL), (3, 20);

SELECT id, value, value IS NULL AS is_null, value = NULL AS equals_null FROM test_null;

Important Notes

NULL means unknown, so any comparison with NULL results in unknown, not true or false.

Always use IS NULL or IS NOT NULL to check for NULL values.

Be careful: 'value <> NULL' or 'value = NULL' will not work as expected.

Summary

NULL means missing or unknown data in databases.

Use IS NULL or IS NOT NULL to check for NULL values.

Comparisons with NULL using = or <> do not work and return unknown.