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.