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

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

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

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

Hint

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

Practice

1. What does NULL represent in a database?

easy

A. Missing or unknown data

B. Zero value

C. Empty string

D. Boolean false

Solution

1. Step 1: Understand the meaning of NULL

   NULL means that the data is missing or unknown, not a specific value like zero or empty string.

2. Step 2: Differentiate NULL from other values

   Zero, empty string, and false are actual values, but NULL means no value is present.

3. Final Answer:

   Missing or unknown data -> Option A

4. Quick Check:

   NULL means missing data = C [OK]

Hint: Remember: NULL means unknown, not zero or empty [OK]

Common Mistakes:

• Thinking NULL is zero
• Confusing NULL with empty string
• Assuming NULL is false

2. Which SQL syntax correctly checks if a column age is NULL?

easy

A. WHERE age = NULL

B. WHERE age == NULL

C. WHERE age IS NULL

D. WHERE age != NULL

Solution

1. Step 1: Recall correct NULL comparison syntax

   In SQL, you cannot use = or != to check for NULL because NULL is not a value.

2. Step 2: Use IS NULL to check for NULL

   The correct way is to use IS NULL to find rows where the column is NULL.

3. Final Answer:

   WHERE age IS NULL -> Option C

4. Quick Check:

   Use IS NULL, not = NULL [OK]

Hint: Use IS NULL, never = NULL to check for NULL [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using == NULL which is invalid in SQL
• Using != NULL which does not work

3. Given a table users with column email, what will this query return?

SELECT * FROM users WHERE email = NULL;

medium

A. All rows where email is NULL

B. No rows

C. Syntax error

D. All rows

Solution

1. Step 1: Understand comparison with NULL using =

   Using = NULL does not work because NULL is not a value and comparisons with NULL return unknown.

2. Step 2: Result of WHERE email = NULL

   The condition email = NULL evaluates to unknown for all rows, so no rows match and none are returned.

3. Final Answer:

   No rows -> Option B

4. Quick Check:

   email = NULL returns no rows [OK]

Hint: = NULL never matches; use IS NULL instead [OK]

Common Mistakes:

• Expecting rows with NULL to be returned
• Thinking = NULL works like IS NULL
• Confusing NULL with empty string

4. You want to find rows where salary is NULL, but this query returns no rows:

SELECT * FROM employees WHERE salary = NULL;

What is the error and how to fix it?

medium

A. Use IS NULL instead of = NULL

B. Use salary = 0 instead of NULL

C. Use salary != NULL instead

D. Add quotes around NULL

Solution

1. Step 1: Identify incorrect NULL comparison

   The query uses = NULL which does not work because NULL is not a value and cannot be compared with =.

2. Step 2: Correct syntax to find NULL values

   Use IS NULL to check for NULL values in SQL.

3. Final Answer:

   Use IS NULL instead of = NULL -> Option A

4. Quick Check:

   IS NULL finds NULLs, = NULL does not [OK]

Hint: Replace = NULL with IS NULL to fix query [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Trying != NULL which also fails
• Using 0 or empty string instead of NULL

5. You want to count how many rows have a missing phone_number in table contacts. Which query correctly counts NULLs?

hard

A. SELECT COUNT(phone_number) FROM contacts WHERE phone_number IS NULL;

B. SELECT COUNT(phone_number) FROM contacts;

C. SELECT COUNT(*) FROM contacts WHERE phone_number = NULL;

D. SELECT COUNT(*) FROM contacts WHERE phone_number IS NULL;

Solution

1. Step 1: Understand counting NULLs

   COUNT(column) counts only non-NULL values, so it ignores NULLs.

2. Step 2: Use WHERE phone_number IS NULL to filter NULL rows

   To count rows with NULL phone_number, filter with IS NULL and count all rows with COUNT(*)

3. Final Answer:

   SELECT COUNT(*) FROM contacts WHERE phone_number IS NULL; -> Option D

4. Quick Check:

   Use IS NULL filter and COUNT(*) to count NULL rows [OK]

Hint: Filter with IS NULL and use COUNT(*) to count NULLs [OK]

Common Mistakes:

• Using = NULL in WHERE clause
• Using COUNT(column) which skips NULLs
• Filtering with COUNT(phone_number) which is invalid