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

Need a 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

Need a 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

Need a 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

Need a hint?

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