Why equals NULL fails in SQL - See It in Action

Understanding Why Equals NULL Fails in SQL

📖 Scenario: You are working with a simple database table that stores information about books in a library. Some books have a known publication year, while others do not, so their publication year is stored as NULL.

🎯 Goal: You will learn why using = NULL in SQL queries does not work as expected and how to correctly check for NULL values using IS NULL.

📋 What You'll Learn

Create a table called books with columns id, title, and publication_year.

Insert sample data into books including some rows where publication_year is NULL.

Write a query that attempts to select books where publication_year = NULL and observe the result.

Write a correct query that selects books where publication_year IS NULL.

💡 Why This Matters

🌍 Real World

Handling NULL values correctly is essential in databases to avoid missing or incorrect data in reports and applications.

💼 Career

Database developers and analysts must understand NULL behavior to write accurate queries and maintain data integrity.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and publication_year (integer). Insert these exact rows into books: (1, 'The Odyssey', 800), (2, 'Unknown Book', NULL), (3, 'Modern SQL', 2020).

SQL

-- Create the books table and insert the rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as specified.

2

Try selecting rows where publication_year = NULL

Write a SQL query to select all columns from books where publication_year = NULL. Use the exact query: SELECT * FROM books WHERE publication_year = NULL;

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  publication_year INTEGER
);

INSERT INTO books (id, title, publication_year) VALUES
  (1, 'The Odyssey', 800),
  (2, 'Unknown Book', NULL),
  (3, 'Modern SQL', 2020);

-- Write a query to select rows where publication_year = NULL
-- Your code here

Hint

Write the query exactly as shown to see that it does not return the row with NULL.

3

Write the correct query using IS NULL

Write a SQL query to select all columns from books where publication_year IS NULL. Use the exact query: SELECT * FROM books WHERE publication_year IS NULL;

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  publication_year INTEGER
);

INSERT INTO books (id, title, publication_year) VALUES
  (1, 'The Odyssey', 800),
  (2, 'Unknown Book', NULL),
  (3, 'Modern SQL', 2020);

SELECT * FROM books WHERE publication_year = NULL;

-- Write the correct query to select rows where publication_year IS NULL
-- Your code here

Hint

Use IS NULL to correctly find rows where the value is NULL.

4

Explain why = NULL fails and finalize

Add a SQL comment explaining that = NULL does not work because NULL means unknown, so comparisons with NULL always return false or unknown. Use the exact comment: -- NULL means unknown, so = NULL does not work; use IS NULL instead

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  publication_year INTEGER
);

INSERT INTO books (id, title, publication_year) VALUES
  (1, 'The Odyssey', 800),
  (2, 'Unknown Book', NULL),
  (3, 'Modern SQL', 2020);

SELECT * FROM books WHERE publication_year = NULL;

SELECT * FROM books WHERE publication_year IS NULL;

-- Add a comment explaining why = NULL fails
-- Your code here

Hint

Write the comment exactly as shown to explain the behavior of NULL in SQL.

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