Why NULL is not a value in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding NULL in SQL

Why is NULL considered not a value in SQL?

A Because NULL is the same as the number zero.

B Because NULL is treated as zero in all calculations.

C Because NULL means the data is missing or unknown, not a specific value.

D Because NULL is a string that represents empty text.

💡 Hint

Think about what NULL represents in a database column when no data is entered.

NULL means the data is missing or unknown. It is not a value like zero or empty string. It represents absence of any value.

❓ query_result

intermediate

Query Result with NULL Comparison

What is the result of this SQL query?

SELECT 1 = NULL AS result;

A NULL

B FALSE

C TRUE

D Syntax Error

💡 Hint

Remember how NULL behaves in comparisons.

Any comparison with NULL returns NULL (unknown), not TRUE or FALSE.

📝 Syntax

advanced

Correct NULL Check in WHERE Clause

Which SQL WHERE clause correctly checks if a column col is NULL?

A WHERE col = NULL

B WHERE col IS NULL

C WHERE col == NULL

D WHERE col != NULL

💡 Hint

Think about how NULL comparisons differ from normal values.

To check for NULL, use IS NULL. Using = NULL or == NULL does not work.

❓ optimization

advanced

Optimizing NULL Checks in SQL Queries

Which option is the most efficient way to filter rows where col is NOT NULL?

A WHERE col IS NOT NULL

B WHERE NOT col = NULL

C WHERE col <> NULL

D WHERE col != NULL

💡 Hint

Consider which syntax is standard and optimized for NULL checks.

IS NOT NULL is the standard and optimized way to check for non-NULL values.

🔧 Debug

expert

Why does this query return no rows?

Given a table users with some NULL values in age, why does this query return no rows?

SELECT * FROM users WHERE age <> NULL;

A Because NULL is treated as zero and no ages are zero.

B Because the table is empty.

C Because the syntax is invalid and causes an error.

D Because <code>age <> NULL</code> always evaluates to NULL, so no rows match.

💡 Hint

Think about how NULL behaves in inequality comparisons.

Comparisons with NULL return NULL (unknown), so age <> NULL never matches any row.

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