IS NULL vs equals NULL in SQL - Hands-On Comparison

Understanding IS NULL vs equals NULL in SQL

📖 Scenario: You are managing a small library database. Some books have a known publication year, but some do not have this information yet, so their publication year is stored as NULL.

🎯 Goal: You will create a table with book data including some NULL values, then write queries to find books with and without publication years using IS NULL and = NULL.

📋 What You'll Learn

Create a table called books with columns id, title, and pub_year

Insert 4 books with exact titles and publication years, including NULL for unknown years

Write a query using IS NULL to find books without a publication year

Write a query using = NULL to find books without a publication year (to see it returns no rows)

Write a query using IS NOT NULL to find books with a known publication year

💡 Why This Matters

🌍 Real World

Handling NULL values is common in databases when some information is missing or unknown, like missing publication years in a library catalog.

💼 Career

Understanding how to query NULL values correctly is essential for data analysis, reporting, and database management jobs.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and pub_year (integer). Then insert these exact rows: (1, 'The Hobbit', 1937), (2, '1984', 1949), (3, 'Unknown Book', NULL), (4, 'Future Book', NULL).

SQL

-- Create table books and insert 4 rows with some NULL pub_year values
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows. Use NULL for unknown publication years.

2

Write a query using IS NULL to find books without a publication year

Write a SQL query that selects all columns from books where pub_year IS NULL to find books without a publication year.

SQL

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

INSERT INTO books (id, title, pub_year) VALUES
  (1, 'The Hobbit', 1937),
  (2, '1984', 1949),
  (3, 'Unknown Book', NULL),
  (4, 'Future Book', NULL);

-- Write a query to select all books where pub_year IS NULL
-- Your code here

Need a hint?

Use IS NULL to check for NULL values in SQL.

3

Write a query using = NULL to find books without a publication year

Write a SQL query that selects all columns from books where pub_year = NULL. This will show that using = NULL does not work as expected.

SQL

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

INSERT INTO books (id, title, pub_year) VALUES
  (1, 'The Hobbit', 1937),
  (2, '1984', 1949),
  (3, 'Unknown Book', NULL),
  (4, 'Future Book', NULL);

SELECT * FROM books WHERE pub_year IS NULL;

-- Write a query to select all books where pub_year = NULL
-- Your code here

Need a hint?

Try using = NULL in the WHERE clause and observe that it returns no rows.

4

Write a query using IS NOT NULL to find books with a known publication year

Write a SQL query that selects all columns from books where pub_year IS NOT NULL to find books that have a known publication year.

SQL

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

INSERT INTO books (id, title, pub_year) VALUES
  (1, 'The Hobbit', 1937),
  (2, '1984', 1949),
  (3, 'Unknown Book', NULL),
  (4, 'Future Book', NULL);

SELECT * FROM books WHERE pub_year IS NULL;

SELECT * FROM books WHERE pub_year = NULL;

-- Write a query to select all books where pub_year IS NOT NULL
-- Your code here

Need a hint?

Use IS NOT NULL to find rows where the column has a value.