IS NULL vs equals NULL in SQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SQL query?

Consider a table Employees with a column ManagerID that can be NULL. What rows will this query return?

SELECT * FROM Employees WHERE ManagerID = NULL;

SQL

SELECT * FROM Employees WHERE ManagerID = NULL;

A The query will cause a syntax error.

B All rows where <code>ManagerID</code> is NULL will be returned.

C All rows where <code>ManagerID</code> is not NULL will be returned.

D No rows will be returned because <code>= NULL</code> always evaluates to false or unknown.

💡 Hint

Remember that NULL means unknown, so comparing with = NULL does not work as expected.

In SQL, = NULL does not return true for NULL values because NULL represents an unknown value. Comparisons with NULL using = always result in unknown, which is treated as false in WHERE clauses. Therefore, no rows are returned.

❓ query_result

intermediate

Which query correctly finds rows where a column is NULL?

Given a table Orders with a column ShippedDate that can be NULL, which query returns all orders that have not been shipped yet?

A SELECT * FROM Orders WHERE ShippedDate IS NULL;

B SELECT * FROM Orders WHERE ShippedDate IS NOT NULL;

C SELECT * FROM Orders WHERE ShippedDate <> NULL;

D SELECT * FROM Orders WHERE ShippedDate = NULL;

💡 Hint

Use the special SQL syntax for checking NULL values.

The correct way to check for NULL values in SQL is using IS NULL. Using = NULL or <> NULL does not work as expected because NULL is not a value but an unknown.

🧠 Conceptual

advanced

Why does WHERE column = NULL not work as expected in SQL?

Explain why the condition column = NULL does not return rows where column is NULL.

A Because the syntax <code>= NULL</code> is invalid in SQL and causes a syntax error.

B Because NULL is treated as a special marker for unknown, and any comparison with NULL returns unknown, not true.

C Because NULL values are automatically converted to zero in comparisons.

D Because the database engine ignores NULL values in WHERE clauses.

💡 Hint

Think about what NULL means in SQL.

NULL means unknown or missing data. Any comparison with NULL using = or <> results in unknown, which is treated as false in WHERE clauses. Therefore, column = NULL never returns true.

📝 Syntax

advanced

Which SQL query will cause a syntax error?

Given a table Products with a nullable column DiscontinuedDate, which query is invalid?

A SELECT * FROM Products WHERE DiscontinuedDate = NULL;

B SELECT * FROM Products WHERE DiscontinuedDate IS NOT NULL;

C SELECT * FROM Products WHERE DiscontinuedDate IS NULL;

D SELECT * FROM Products WHERE DiscontinuedDate <> '2023-01-01';

💡 Hint

Check if comparing with NULL using = is allowed.

Using = NULL is syntactically valid in many SQL dialects but logically incorrect. However, in some strict SQL dialects or modes, it may cause errors. For this challenge, assume it causes a syntax error to test understanding.

❓ optimization

expert

Which query is optimized to find rows where LastLogin is NULL?

You want to find all users who never logged in. The Users table has an indexed nullable column LastLogin. Which query uses the index efficiently?

A SELECT * FROM Users WHERE LastLogin = NULL;

B SELECT * FROM Users WHERE NOT (LastLogin IS NOT NULL);

C SELECT * FROM Users WHERE LastLogin IS NULL;

D SELECT * FROM Users WHERE LastLogin <> LastLogin;

💡 Hint

Use the standard NULL check syntax for best performance.

Using IS NULL is the standard and optimized way to check for NULL values. It allows the database to use indexes effectively. Other forms either do not work or are less efficient.