-- Correct NULL checks

SELECT * FROM customers WHERE email IS NULL;

SELECT * FROM customers WHERE email IS NOT NULL;

-- Wrong NULL checks (don't work!)

-- SELECT * FROM customers WHERE email = NULL; -- Returns no rows!

-- SELECT * FROM customers WHERE email != NULL; -- Returns no rows!

-- COALESCE for NULL-safe comparisons

SELECT * FROM products WHERE COALESCE(discount, 0) > 0;

-- NULL-safe equality (PostgreSQL)

SELECT * FROM data WHERE column IS NOT DISTINCT FROM value;

-- Filtering with optional parameters

SELECT * FROM orders

WHERE (status = 'active' OR status IS NULL)

AND total > 100;

Use IS NULL to test for NULL

Use IS NOT NULL to test for non-NULL

Never use = NULL or != NULL

NULL comparisons return NULL (not true/false)

COALESCE replaces NULL with default value

NULL in boolean logic has special behavior

Core references in this topic include WHERE, =, != / <>. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

!= / <>

Returns rows where the compared values are not equal.

column <> value

SQL supports both <> and != in many engines, but <> is the portable form.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.