how do you compare null values in sql

In SQL, NULL is a special marker that represents missing or undefined data, and comparing values to NULL requires special syntax. Standard comparison operators (=, <>, <, >, <=, >=) do not work as expected with NULL because NULL represents an unknown value. When you use these operators with NULL, the result is always unknown (neither true nor false), which is treated as false in conditional logic.

To properly test whether a value is NULL, use the IS NULL or IS NOT NULL operators:

SELECT * FROM users WHERE email IS NULL;SELECT * FROM users WHERE phone IS NOT NULL;

For example, email = NULL will never return any rows, even if the email column contains NULL values. This is because NULL represents an unknown value, and comparing an unknown value to anything (even another unknown) produces an unknown result.

Some SQL systems provide additional functions to handle NULL comparisons, such as ISNULL() or COALESCE(), which allow you to replace NULL with a default value before comparison:

SELECT * FROM users WHERE COALESCE(email, '') = '';

This three-valued logic (true, false, unknown) is fundamental to SQL's handling of NULL and is defined in the SQL standard. Understanding this behavior is essential for writing correct queries, as many unexpected results in SQL stem from improper NULL handling.