Why NULL is not a value in SQL - Visual Breakdown

Concept Flow - Why NULL is not a value

Start with NULL

↓

Check if NULL is a value

No↓

NULL means unknown or missing

↓

Operations with NULL result in NULL or UNKNOWN

↓

NULL is not equal to anything, even NULL

↓

Use IS NULL or IS NOT NULL to check NULL

This flow shows that NULL is not a value but a marker for unknown or missing data, affecting how SQL treats it in comparisons and operations.

Execution Sample

SQL

SELECT NULL = NULL AS EqualCheck,
       NULL IS NULL AS IsNullCheck;

This query shows that NULL compared to NULL is not true, but IS NULL returns true.

Execution Table

Step	Expression	Evaluation	Result
1	NULL = NULL	Is NULL equal to NULL?	NULL (unknown)
2	NULL IS NULL	Is NULL actually NULL?	TRUE
3	NULL = 5	Is NULL equal to 5?	NULL (unknown)
4	NULL <> 5	Is NULL not equal to 5?	NULL (unknown)
5	NULL IS NOT NULL	Is NULL not NULL?	FALSE

💡 SQL treats NULL as unknown, so comparisons with NULL return NULL (unknown), not TRUE or FALSE.

Variable Tracker

Expression	Result
NULL = NULL	NULL (unknown)
NULL IS NULL	TRUE
NULL = 5	NULL (unknown)
NULL <> 5	NULL (unknown)
NULL IS NOT NULL	FALSE

Key Moments - 3 Insights

Why does 'NULL = NULL' not return TRUE?

Because NULL means unknown, SQL cannot say two unknowns are equal, so it returns NULL (unknown), as shown in execution_table step 1.

How do we properly check if a value is NULL?

Use 'IS NULL' or 'IS NOT NULL' operators, which return TRUE or FALSE, unlike '=' operator. See execution_table step 2.

What happens when NULL is compared to a number?

Any comparison with NULL returns NULL (unknown), not TRUE or FALSE, as shown in steps 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of 'NULL = NULL' at step 1?

A NULL (unknown)

B FALSE

C TRUE

D Error

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the query confirm a value is NULL?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look for 'IS NULL' operator in execution_table.

If you replace 'NULL = 5' with 'NULL IS NULL', what would the result be?

A NULL (unknown)

B FALSE

C TRUE

D Error

💡 Hint

Refer to execution_table step 2 for 'NULL IS NULL' result.

Concept Snapshot

NULL is not a value but a marker for unknown or missing data.
Comparisons with NULL using '=' or '<>' return NULL (unknown), not TRUE or FALSE.
Use 'IS NULL' or 'IS NOT NULL' to check for NULL explicitly.
NULL = NULL returns NULL, but NULL IS NULL returns TRUE.
This affects how SQL queries handle missing information.

Full Transcript

In SQL, NULL represents unknown or missing data, not a real value. When you compare NULL to anything, even another NULL, the result is not TRUE or FALSE but NULL, meaning unknown. To check if a value is NULL, you must use 'IS NULL' or 'IS NOT NULL' operators, which return TRUE or FALSE. This behavior ensures SQL handles missing information carefully without assuming equality or inequality. For example, 'NULL = NULL' returns NULL, but 'NULL IS NULL' returns TRUE. Understanding this helps avoid mistakes in SQL queries involving NULLs.

Practice

1. What does NULL represent in a database?

easy

A. Missing or unknown data

B. Zero value

C. Empty string

D. Boolean false

Solution

1. Step 1: Understand the meaning of NULL

   NULL means that the data is missing or unknown, not a specific value like zero or empty string.

2. Step 2: Differentiate NULL from other values

   Zero, empty string, and false are actual values, but NULL means no value is present.

3. Final Answer:

   Missing or unknown data -> Option A

4. Quick Check:

   NULL means missing data = C [OK]

Hint: Remember: NULL means unknown, not zero or empty [OK]

Common Mistakes:

• Thinking NULL is zero
• Confusing NULL with empty string
• Assuming NULL is false

2. Which SQL syntax correctly checks if a column age is NULL?

easy

A. WHERE age = NULL

B. WHERE age == NULL

C. WHERE age IS NULL

D. WHERE age != NULL

Solution

1. Step 1: Recall correct NULL comparison syntax

   In SQL, you cannot use = or != to check for NULL because NULL is not a value.

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

   The correct way is to use IS NULL to find rows where the column is NULL.

3. Final Answer:

   WHERE age IS NULL -> Option C

4. Quick Check:

   Use IS NULL, not = NULL [OK]

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

Common Mistakes:

• Using = NULL instead of IS NULL
• Using == NULL which is invalid in SQL
• Using != NULL which does not work

3. Given a table users with column email, what will this query return?

SELECT * FROM users WHERE email = NULL;

medium

A. All rows where email is NULL

B. No rows

C. Syntax error

D. All rows

Solution

1. Step 1: Understand comparison with NULL using =

   Using = NULL does not work because NULL is not a value and comparisons with NULL return unknown.

2. Step 2: Result of WHERE email = NULL

   The condition email = NULL evaluates to unknown for all rows, so no rows match and none are returned.

3. Final Answer:

   No rows -> Option B

4. Quick Check:

   email = NULL returns no rows [OK]

Hint: = NULL never matches; use IS NULL instead [OK]

Common Mistakes:

• Expecting rows with NULL to be returned
• Thinking = NULL works like IS NULL
• Confusing NULL with empty string

4. You want to find rows where salary is NULL, but this query returns no rows:

SELECT * FROM employees WHERE salary = NULL;

What is the error and how to fix it?

medium

A. Use IS NULL instead of = NULL

B. Use salary = 0 instead of NULL

C. Use salary != NULL instead

D. Add quotes around NULL

Solution

1. Step 1: Identify incorrect NULL comparison

   The query uses = NULL which does not work because NULL is not a value and cannot be compared with =.

2. Step 2: Correct syntax to find NULL values

   Use IS NULL to check for NULL values in SQL.

3. Final Answer:

   Use IS NULL instead of = NULL -> Option A

4. Quick Check:

   IS NULL finds NULLs, = NULL does not [OK]

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

Common Mistakes:

• Using = NULL instead of IS NULL
• Trying != NULL which also fails
• Using 0 or empty string instead of NULL

5. You want to count how many rows have a missing phone_number in table contacts. Which query correctly counts NULLs?

hard

A. SELECT COUNT(phone_number) FROM contacts WHERE phone_number IS NULL;

B. SELECT COUNT(phone_number) FROM contacts;

C. SELECT COUNT(*) FROM contacts WHERE phone_number = NULL;

D. SELECT COUNT(*) FROM contacts WHERE phone_number IS NULL;

Solution

1. Step 1: Understand counting NULLs

   COUNT(column) counts only non-NULL values, so it ignores NULLs.

2. Step 2: Use WHERE phone_number IS NULL to filter NULL rows

   To count rows with NULL phone_number, filter with IS NULL and count all rows with COUNT(*)

3. Final Answer:

   SELECT COUNT(*) FROM contacts WHERE phone_number IS NULL; -> Option D

4. Quick Check:

   Use IS NULL filter and COUNT(*) to count NULL rows [OK]

Hint: Filter with IS NULL and use COUNT(*) to count NULLs [OK]

Common Mistakes:

• Using = NULL in WHERE clause
• Using COUNT(column) which skips NULLs
• Filtering with COUNT(phone_number) which is invalid