NULL behavior in comparisons in SQL - Mini Project: Build & Apply

Understanding NULL Behavior in Comparisons

📖 Scenario: You are managing a small customer database for a local store. Some customers have provided their email addresses, but others have not, so their email field is NULL. You want to learn how SQL treats NULL values when you compare them to other values.

🎯 Goal: Build a simple SQL query setup to explore how NULL behaves in comparisons, especially with = and IS NULL.

📋 What You'll Learn

Create a table called customers with columns id (integer) and email (text, nullable).

Insert exactly three rows into customers: one with email '[e-mail]', one with email NULL, and one with email '[e-mail]'.

Write a query to select all customers where email = NULL.

Write a query to select all customers where email IS NULL.

💡 Why This Matters

🌍 Real World

Handling missing or unknown data is common in databases. Knowing how NULL behaves helps avoid bugs in queries.

💼 Career

Database developers and analysts must understand NULL comparisons to write correct and efficient SQL queries.

1

Create the customers table

Write a SQL statement to create a table called customers with two columns: id as an integer primary key, and email as a text column that can be NULL.

SQL

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

Need a hint?

Use CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT); to create the table.

2

Insert three rows into customers

Insert three rows into customers with these exact values: (1, '[e-mail]'), (2, NULL), and (3, '[e-mail]').

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT
);
-- Insert the three rows below
-- Your code here

Need a hint?

Use a single INSERT INTO customers (id, email) VALUES statement with three rows.

3

Query customers where email = NULL

Write a SQL query to select all columns from customers where email = NULL.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (id, email) VALUES
(1, '[e-mail]'),
(2, NULL),
(3, '[e-mail]');

-- Write the SELECT query where email = NULL
-- Your code here

Need a hint?

Use SELECT * FROM customers WHERE email = NULL; to see how SQL treats this comparison.

4

Query customers where email IS NULL

Write a SQL query to select all columns from customers where email IS NULL.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (id, email) VALUES
(1, '[e-mail]'),
(2, NULL),
(3, '[e-mail]');

SELECT * FROM customers WHERE email = NULL;

-- Write the SELECT query where email IS NULL
-- Your code here

Need a hint?

Use SELECT * FROM customers WHERE email IS NULL; to correctly find rows with NULL emails.