Why NULL is not a value in SQL - See It in Action

Understanding Why NULL is Not a Value in SQL

📖 Scenario: Imagine you are managing a small library database. Some books have known publication years, but others do not. You want to understand how SQL treats unknown or missing information using NULL.

🎯 Goal: You will create a simple table with book information, insert data including NULL for unknown publication years, and write queries to see how NULL behaves differently from normal values.

📋 What You'll Learn

Create a table called Books with columns BookID (integer), Title (text), and PublicationYear (integer).

Insert three rows into Books with one row having NULL for PublicationYear.

Write a query to select all books where PublicationYear is NULL.

Write a query to select all books where PublicationYear is not NULL.

💡 Why This Matters

🌍 Real World

Handling missing or unknown data is common in real databases, like customer info or product details.

💼 Career

Understanding NULL is essential for writing correct SQL queries and avoiding bugs in data analysis or software development.

1

Create the Books table

Write a SQL statement to create a table called Books with columns BookID as INTEGER, Title as TEXT, and PublicationYear as INTEGER.

SQL

-- Write the CREATE TABLE statement for Books
-- Your code here

Need a hint?

Use CREATE TABLE Books (BookID INTEGER, Title TEXT, PublicationYear INTEGER);

2

Insert book data including NULL

Insert three rows into Books with these exact values: (1, 'The Odyssey', 800), (2, 'Unknown Book', NULL), and (3, 'Modern SQL', 2020).

SQL

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  PublicationYear INTEGER
);
-- Insert three rows into Books including one with NULL PublicationYear
-- Your code here

Need a hint?

Use INSERT INTO Books (BookID, Title, PublicationYear) VALUES (1, 'The Odyssey', 800), (2, 'Unknown Book', NULL), (3, 'Modern SQL', 2020);

3

Query books with NULL PublicationYear

Write a SQL query to select all columns from Books where PublicationYear is NULL using IS NULL.

SQL

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  PublicationYear INTEGER
);

INSERT INTO Books (BookID, Title, PublicationYear) VALUES
  (1, 'The Odyssey', 800),
  (2, 'Unknown Book', NULL),
  (3, 'Modern SQL', 2020);

-- Select all books where PublicationYear is NULL
-- Your code here

Need a hint?

Use SELECT * FROM Books WHERE PublicationYear IS NULL; to find rows with unknown years.

4

Query books with known PublicationYear

Write a SQL query to select all columns from Books where PublicationYear is not NULL using IS NOT NULL.

SQL

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  PublicationYear INTEGER
);

INSERT INTO Books (BookID, Title, PublicationYear) VALUES
  (1, 'The Odyssey', 800),
  (2, 'Unknown Book', NULL),
  (3, 'Modern SQL', 2020);

SELECT * FROM Books WHERE PublicationYear IS NULL;

-- Select all books where PublicationYear is NOT NULL
-- Your code here

Need a hint?

Use SELECT * FROM Books WHERE PublicationYear IS NOT NULL; to find rows with known years.