Why equals NULL fails in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why does comparing with NULL using '=' fail?

In SQL, why does the expression column = NULL not return rows where column is NULL?

A Because NULL represents an unknown value, and '=' cannot compare unknowns, so the result is neither true nor false.

B Because NULL is treated as zero in SQL, so '=' compares to zero instead of NULL.

C Because NULL is a string value and '=' only works with numbers.

D Because NULL is automatically converted to an empty string, causing '=' to fail.

💡 Hint

Think about what NULL means in SQL and how comparisons work with unknown values.

In SQL, NULL means unknown or missing data. Comparing anything to NULL with '=' does not return true or false but unknown, so no rows match. To check for NULL, use IS NULL.

❓ query_result

intermediate

What is the output of this query?

Given a table users with a column email that contains some NULL values, what rows will this query return?

SELECT * FROM users WHERE email = NULL;

A The query will produce a syntax error.

B All rows where email is NULL will be returned.

C No rows will be returned.

D All rows where email is not NULL will be returned.

💡 Hint

Remember how '=' works with NULL in SQL.

The condition email = NULL always evaluates to unknown, so no rows satisfy it. To find NULLs, use email IS NULL.

📝 Syntax

advanced

Which query correctly finds rows with NULL values?

Choose the SQL query that correctly returns rows where the status column is NULL.

A SELECT * FROM orders WHERE status IS NULL;

B SELECT * FROM orders WHERE status = NULL;

C SELECT * FROM orders WHERE status == NULL;

D SELECT * FROM orders WHERE status != NULL;

💡 Hint

Think about the special syntax for NULL comparisons in SQL.

Only IS NULL correctly checks for NULL values. '=' and '==' do not work with NULL, and '!=' also does not find NULLs.

❓ optimization

advanced

Why is using 'IS NULL' better than 'column = NULL' in WHERE clauses?

Consider performance and correctness. Why should you use IS NULL instead of = NULL in SQL WHERE clauses?

A Because '= NULL' automatically converts NULLs to empty strings.

B Because 'IS NULL' is optimized by the database engine to quickly find NULLs, while '= NULL' never matches and wastes resources.

C Because 'IS NULL' converts NULLs to zeros before comparison.

D Because '= NULL' is faster but less readable than 'IS NULL'.

💡 Hint

Think about how the database engine treats NULL comparisons internally.

'IS NULL' is the correct and optimized way to check for NULLs. '= NULL' never matches and can cause inefficient scans or confusion.

🔧 Debug

expert

What error or result occurs with this query?

Given a table products with a nullable column discount, what happens when running this query?

SELECT * FROM products WHERE discount = NULL;

A The query returns all rows regardless of discount value.

B The query returns all rows where discount is NULL.

C The query raises a syntax error due to invalid NULL comparison.

D The query returns zero rows because 'discount = NULL' evaluates to unknown for all rows.

💡 Hint

Recall how NULL comparisons behave in SQL.

Comparing with '=' to NULL always yields unknown, so no rows match. No syntax error occurs.

Practice

1. Why does the SQL condition column = NULL always fail to find rows with NULL values?

easy

A. Because NULL means unknown and cannot be compared with = or != operators

B. Because NULL is treated as zero in SQL

C. Because NULL is a string and needs quotes

D. Because NULL values are automatically excluded from queries

Solution

1. Step 1: Understand NULL meaning in SQL

   NULL represents an unknown or missing value, not a specific value.

2. Step 2: Explain comparison behavior with NULL

   Comparing anything to NULL with = or != always returns false because NULL is unknown.

3. Final Answer:

   Because NULL means unknown and cannot be compared with = or != operators -> Option A

4. Quick Check:

   NULL comparison with = fails [OK]

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

Common Mistakes:

• Using = NULL to find NULL values
• Assuming NULL equals zero
• Trying to quote NULL as a string

2. Which of the following SQL WHERE clauses correctly checks for NULL values in the age column?

easy

A. WHERE age != NULL

B. WHERE age = NULL

C. WHERE age IS NULL

D. WHERE age = 'NULL'

Solution

1. Step 1: Review correct syntax to check NULL

   SQL uses IS NULL to test if a column has NULL values.

2. Step 2: Analyze each option

   WHERE age = NULL and C use = or != with NULL which always fail; WHERE age = 'NULL' treats NULL as a string which is incorrect.

3. Final Answer:

   WHERE age IS NULL -> Option C

4. Quick Check:

   Use IS NULL for NULL checks [OK]

Hint: Always use IS NULL or IS NOT NULL for NULL checks [OK]

Common Mistakes:

• Using = NULL or != NULL in WHERE clause
• Quoting NULL as a string
• Confusing NULL with empty string

3. Given a table users with a column email that contains some NULL values, what will this query return?

SELECT * FROM users WHERE email = NULL;

medium

A. All rows where email is NULL

B. All rows where email is not NULL

C. Syntax error

D. No rows at all

Solution

1. Step 1: Understand the effect of = NULL in WHERE clause

   Comparing with = NULL always yields unknown (false), so no rows match.

2. Step 2: Predict query result

   The query returns zero rows because no row satisfies email = NULL.

3. Final Answer:

   No rows at all -> Option D

4. Quick Check:

   email = NULL returns no rows [OK]

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

Common Mistakes:

• Expecting rows with NULL to be returned
• Thinking = NULL is valid syntax
• Confusing NULL with empty string

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

SELECT * FROM employees WHERE salary = NULL;

But it returns no rows. What is the fix?

medium

A. Use WHERE salary != NULL

B. Change to WHERE salary IS NULL

C. Add quotes: WHERE salary = 'NULL'

D. Change to WHERE salary = 0

Solution

1. Step 1: Identify why salary = NULL fails

   Comparing with = NULL always returns false, so no rows match.

2. Step 2: Use correct syntax to check NULL

   Use IS NULL to find rows where salary is NULL.

3. Final Answer:

   Change to WHERE salary IS NULL -> Option B

4. Quick Check:

   Use IS NULL, not = NULL [OK]

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

Common Mistakes:

• Using = NULL instead of IS NULL
• Comparing NULL to zero
• Quoting NULL as string

5. You want to select all rows from orders where shipped_date is either NULL or equals '2024-01-01'. Which WHERE clause is correct?

hard

A. WHERE shipped_date IS NULL OR shipped_date = '2024-01-01'

B. WHERE shipped_date = NULL OR shipped_date = '2024-01-01'

C. WHERE shipped_date = 'NULL' OR shipped_date = '2024-01-01'

D. WHERE shipped_date != NULL OR shipped_date = '2024-01-01'

Solution

1. Step 1: Understand NULL comparison rules

   Use IS NULL to check for NULL values, not = NULL or != NULL.

2. Step 2: Analyze the WHERE clause options

   WHERE shipped_date IS NULL OR shipped_date = '2024-01-01' correctly uses IS NULL and equals comparison; others misuse NULL comparisons or treat NULL as string.

3. Final Answer:

   WHERE shipped_date IS NULL OR shipped_date = '2024-01-01' -> Option A

4. Quick Check:

   Combine IS NULL with = for dates [OK]

Hint: Use IS NULL for NULL, = for values in OR conditions [OK]

Common Mistakes:

• Using = NULL or != NULL in OR conditions
• Quoting NULL as string
• Mixing NULL checks with wrong operators