IS NULL vs equals NULL in SQL - Visual Side-by-Side Comparison

Concept Flow - IS NULL vs equals NULL

Start Query

↓

Check Condition

↓

Return True

↓

Filter Rows

↓

Result Set

The query checks each row's column with IS NULL or = NULL. IS NULL returns true for NULLs, = NULL returns unknown, so no rows match.

Execution Sample

SQL

SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email = NULL;

Two queries: one finds rows where email is NULL, the other tries to find rows where email equals NULL but returns no rows.

Execution Table

Step	Row email value	Condition	Evaluation	Row Included in Result?
1	NULL	email IS NULL	TRUE	Yes
2	NULL	email = NULL	UNKNOWN (treated as FALSE)	No
3	'[e-mail]'	email IS NULL	FALSE	No
4	'[e-mail]'	email = NULL	UNKNOWN (treated as FALSE)	No
5	'' (empty string)	email IS NULL	FALSE	No
6	'' (empty string)	email = NULL	UNKNOWN (treated as FALSE)	No

💡 All rows checked; only rows with email IS NULL condition TRUE are included.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6
email	N/A	NULL	NULL	'[e-mail]'	'[e-mail]'	''	''
email IS NULL	N/A	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
email = NULL	N/A	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

Key Moments - 2 Insights

Why does 'email = NULL' never return any rows even if some emails are NULL?

Because in SQL, comparing anything to NULL with '=' results in UNKNOWN, which is treated as FALSE in WHERE filters. See execution_table rows 2,4,6.

What does 'IS NULL' check differently than '='?

'IS NULL' explicitly checks if a value is NULL and returns TRUE for NULLs, allowing those rows to be included. See execution_table rows 1 and 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the evaluation of 'email = NULL' when email is NULL?

A TRUE

B FALSE

C UNKNOWN

D NULL

💡 Hint

Check row 2 in the execution_table under 'Evaluation' for 'email = NULL' with email NULL.

At which step does 'email IS NULL' return TRUE?

A Step 1

B Step 3

C Step 5

D Step 6

💡 Hint

Look at execution_table rows where 'email IS NULL' is TRUE.

If we replace 'email = NULL' with 'email IS NULL', how would the result change?

A No rows would be returned

B Only rows with NULL emails would be returned

C All rows would be returned

D Only rows with empty string emails would be returned

💡 Hint

Refer to the difference in evaluation between 'email = NULL' and 'email IS NULL' in the execution_table.

Concept Snapshot

IS NULL checks if a value is NULL and returns TRUE for NULLs.
Using '=' to compare with NULL returns UNKNOWN, treated as FALSE.
Use IS NULL to find NULL values in SQL queries.
Example: WHERE column IS NULL returns rows with NULLs.
WHERE column = NULL returns no rows.
Always use IS NULL for NULL checks.

Full Transcript

This visual execution shows how SQL treats NULL comparisons. When checking if a column is NULL, use IS NULL because it returns TRUE for NULL values. Using '=' to compare with NULL returns UNKNOWN, which SQL treats as FALSE, so no rows match. The execution table shows rows with NULL emails pass the IS NULL check but fail the '=' check. This is why IS NULL is the correct way to find NULLs in SQL.

Practice

1. Which SQL expression correctly checks if a column age has missing (NULL) values?

easy

A. age IS NULL

B. age = NULL

C. age == NULL

D. age != NULL

Solution

1. Step 1: Understand NULL in SQL

   NULL means unknown or missing data, and it cannot be compared with = or != operators.

2. Step 2: Use IS NULL to check for missing values

   The correct way to check for NULL is using IS NULL, which returns true for missing values.

3. Final Answer:

   age IS NULL -> Option A

4. Quick Check:

   Use IS NULL to check missing values [OK]

Hint: Use IS NULL, never = NULL to check missing data [OK]

Common Mistakes:

• Using = NULL returns no rows
• Confusing IS NULL with equality operator
• Using == NULL which is invalid in SQL

2. Which of the following SQL WHERE clauses is syntactically correct to find rows where salary is NULL?

easy

A. WHERE salary IS NULL

B. WHERE salary = NULL

C. WHERE salary == NULL

D. WHERE salary != NULL

Solution

1. Step 1: Review SQL syntax for NULL checks

   SQL uses IS NULL to check for NULL values; equality operators (=, ==) are invalid here.

2. Step 2: Identify the correct syntax

   Only WHERE salary IS NULL is valid and will run without error.

3. Final Answer:

   WHERE salary IS NULL -> Option A

4. Quick Check:

   IS NULL is correct syntax for NULL checks [OK]

Hint: IS NULL is the only valid syntax for NULL checks [OK]

Common Mistakes:

• Using = NULL causes syntax errors or no matches
• Using == NULL is invalid SQL
• Using != NULL does not find NULLs

3. Given the table employees with column manager_id that can be NULL, what will this query return?

SELECT * FROM employees WHERE manager_id = NULL;

medium

A. All rows where manager_id is NULL

B. Syntax error

C. No rows

D. All rows where manager_id is not NULL

Solution

1. Step 1: Understand comparison with NULL using = operator

   In SQL, = NULL does not match any rows because NULL is unknown and cannot be compared with =.

2. Step 2: Result of the query

   The query returns zero rows because no value equals NULL using = operator.

3. Final Answer:

   No rows -> Option C

4. Quick Check:

   = NULL returns no rows [OK]

Hint: = NULL never matches any row, returns empty result [OK]

Common Mistakes:

• Expecting = NULL to find NULL values
• Confusing NULL with empty string or zero
• Assuming syntax error instead of empty result

4. You wrote this query to find rows with NULL in email:

SELECT * FROM users WHERE email = NULL;

But it returns no rows. What is the fix?

medium

A. Use email IS NOT NULL

B. Change to email != NULL

C. Add quotes: email = 'NULL'

D. Change to email IS NULL

Solution

1. Step 1: Identify incorrect NULL comparison

   Using = NULL does not find NULL values because NULL is not equal to anything.

2. Step 2: Correct NULL check syntax

   Use IS NULL to properly find rows where email is missing.

3. Final Answer:

   Change to email IS NULL -> Option D

4. Quick Check:

   Use IS NULL to find missing values [OK]

Hint: Replace = NULL with IS NULL to fix query [OK]

Common Mistakes:

• Using != NULL expecting to find NULLs
• Using string 'NULL' instead of NULL keyword
• Using IS NOT NULL which finds non-missing values

5. You want to select all customers who have no recorded phone number or whose phone number is an empty string. Which WHERE clause correctly finds these rows?

SELECT * FROM customers WHERE ???

hard

A. phone = NULL OR phone = ''

B. phone IS NULL OR phone = ''

C. phone IS NULL AND phone = ''

D. phone = NULL AND phone = ''

Solution

1. Step 1: Understand NULL and empty string difference

   NULL means missing data, empty string means data is present but empty.

2. Step 2: Combine conditions correctly

   Use phone IS NULL to find missing phone numbers and phone = '' to find empty strings, combined with OR to get either case.

3. Final Answer:

   phone IS NULL OR phone = '' -> Option B

4. Quick Check:

   Use IS NULL for missing, = '' for empty [OK]

Hint: Use IS NULL OR = '' to find missing or empty values [OK]

Common Mistakes:

• Using = NULL which never matches
• Using AND instead of OR excludes all rows
• Confusing empty string with NULL