Why NULL is not a value in SQL - Quick Recap

Recall & Review

beginner

What does NULL represent in a database?

NULL represents the absence of any value or unknown data in a database field. It means 'no data' rather than zero or empty.

beginner

Is NULL the same as zero or an empty string?

No, NULL is not zero or an empty string. Zero and empty string are actual values, but NULL means no value is present or it is unknown.

intermediate

Why can't NULL be treated as a value in SQL comparisons?

Because NULL means unknown, comparing NULL to any value (including NULL) results in UNKNOWN, not TRUE or FALSE. This is why special checks like IS NULL are needed.

beginner

How do you check if a column contains NULL in SQL?

Use the condition column IS NULL to check if a column contains NULL values.

intermediate

What happens when you use = NULL in a SQL WHERE clause?

Using = NULL always returns no rows because NULL is not a value and comparisons with NULL result in UNKNOWN.

What does NULL mean in a database?

A No data or unknown value

B Zero value

C Empty string

D False boolean

NULL means no data or unknown value, not zero or empty string.

Which SQL condition correctly checks for NULL values?

A column = NULL

B column == NULL

C column != NULL

D column IS NULL

Use 'IS NULL' to check for NULL values in SQL.

What is the result of comparing NULL to any value using '='?

A FALSE

B UNKNOWN

C TRUE

D Error

Comparisons with NULL return UNKNOWN, not TRUE or FALSE.

Is NULL considered a value in SQL?

A Yes, it means zero

B Yes, it is a value

C No, it means absence of value

D No, it means empty string

NULL means absence of a value, not a value itself.

Which of these is true about NULL?

A NULL = NULL is UNKNOWN

B NULL = NULL is FALSE

C NULL = NULL causes an error

D NULL = NULL is TRUE

NULL compared to NULL results in UNKNOWN in SQL.

Explain why NULL is not considered a value in SQL and how it affects comparisons.

Think about what NULL represents and how SQL treats it differently from normal values.

Describe how to properly check for NULL values in a SQL query and why using '=' does not work.

Remember that NULL means unknown, so normal equality checks fail.

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