WHERE with IS NULL and IS NOT NULL in SQL - Mini Project: Build & Apply

Filter Database Records Using WHERE with IS NULL and IS NOT NULL

📖 Scenario: You are managing a small customer database for a local store. Some customers have provided their email addresses, but others have not. You want to learn how to find customers who have or have not shared their email addresses.

🎯 Goal: Build SQL queries step-by-step to filter customers based on whether their email field is NULL or not.

📋 What You'll Learn

Create a table called customers with columns id, name, and email.

Insert specific customer records with some email values as NULL.

Write a query to select customers where email is NULL.

Write a query to select customers where email is NOT NULL.

💡 Why This Matters

🌍 Real World

Filtering records with missing or present information is common in real databases, such as finding customers who have not provided contact details.

💼 Career

Database professionals often write queries to handle NULL values correctly to ensure accurate data analysis and reporting.

1

Create the customers table

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

SQL

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

Hint

Use CREATE TABLE customers and define id INTEGER PRIMARY KEY, name TEXT, and email TEXT. Remember, email can be NULL by default.

2

Insert customer records with some emails NULL

Insert these exact three customers into the customers table: (1, 'Alice', '[e-mail]'), (2, 'Bob', NULL), and (3, 'Charlie', '[e-mail]'). Use three separate INSERT statements.

SQL

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

Hint

Use three INSERT INTO customers (id, name, email) VALUES (...) statements. For Bob, set email to NULL without quotes.

3

Select customers with NULL emails

Write a SQL query to select all columns from customers where the email is NULL. Use WHERE email IS NULL.

SQL

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

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

-- Select customers where email is NULL
-- Your code here

Hint

Use SELECT * FROM customers WHERE email IS NULL; to find customers without emails.

4

Select customers with NOT NULL emails

Write a SQL query to select all columns from customers where the email is NOT NULL. Use WHERE email IS NOT NULL.

SQL

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

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

SELECT * FROM customers WHERE email IS NULL;

-- Select customers where email is NOT NULL
-- Your code here

Hint

Use SELECT * FROM customers WHERE email IS NOT NULL; to find customers who have emails.

Practice

1. Which SQL clause correctly finds rows where the email column has no value stored (is missing)?

easy

A. WHERE email = NULL

B. WHERE email = ''

C. WHERE email IS NULL

D. WHERE email IS NOT NULL

Solution

1. Step 1: Understand NULL meaning in SQL

   NULL means no value or missing data, not an empty string or zero.

2. Step 2: Use correct syntax to check NULL

   To check for NULL, use IS NULL, not = NULL or empty string.

3. Final Answer:

   WHERE email IS NULL -> Option C

4. Quick Check:

   Check NULL with IS NULL = WHERE email IS NULL [OK]

Hint: Use IS NULL to find missing values, never = NULL [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Confusing empty string '' with NULL
• Using IS NOT NULL to find missing values

2. Which of the following is the correct syntax to select rows where the phone column is NOT missing?

easy

A. SELECT * FROM contacts WHERE phone IS NOT NULL;

B. SELECT * FROM contacts WHERE phone = NOT NULL;

C. SELECT * FROM contacts WHERE phone != NULL;

D. SELECT * FROM contacts WHERE phone NOT NULL;

Solution

1. Step 1: Recall correct syntax for NOT NULL check

   SQL uses IS NOT NULL to find rows where value exists.

2. Step 2: Identify syntax errors in other options

   Options B, C, D use invalid syntax like = NOT NULL or != NULL or missing IS.

3. Final Answer:

   SELECT * FROM contacts WHERE phone IS NOT NULL; -> Option A

4. Quick Check:

   Use IS NOT NULL to find existing values = SELECT * FROM contacts WHERE phone IS NOT NULL; [OK]

Hint: Use IS NOT NULL, not = NOT NULL or != NULL [OK]

Common Mistakes:

• Using = NOT NULL instead of IS NOT NULL
• Using != NULL which is invalid
• Omitting IS keyword

3. Given the table employees with columns id, name, and manager_id where some employees have no manager (NULL), what will this query return?

SELECT name FROM employees WHERE manager_id IS NULL;

medium

A. All employees who have a manager assigned

B. All employees who do not have a manager assigned

C. All employees including those with and without managers

D. Syntax error due to IS NULL usage

Solution

1. Step 1: Understand IS NULL condition

   The condition manager_id IS NULL filters rows where manager_id has no value (missing).

2. Step 2: Interpret query result

   The query returns names of employees without any manager assigned (NULL manager_id).

3. Final Answer:

   All employees who do not have a manager assigned -> Option B

4. Quick Check:

   IS NULL filters missing values = All employees who do not have a manager assigned [OK]

Hint: IS NULL finds missing values, so returns employees without managers [OK]

Common Mistakes:

• Thinking IS NULL finds rows with values
• Confusing NULL with empty string
• Assuming syntax error with IS NULL

4. Identify the error in this query that tries to select rows where address is NOT NULL:

SELECT * FROM customers WHERE address != NULL;

medium

A. Should use = NULL instead of != NULL

B. Missing quotes around NULL

C. No error; query is correct

D. Using != NULL is invalid; should use IS NOT NULL

Solution

1. Step 1: Check NULL comparison syntax

   In SQL, NULL cannot be compared with = or != operators.

2. Step 2: Use correct syntax for NOT NULL

   The correct way is IS NOT NULL to check for non-missing values.

3. Final Answer:

   Using != NULL is invalid; should use IS NOT NULL -> Option D

4. Quick Check:

   Use IS NOT NULL, not != NULL [OK]

Hint: Never use = or != with NULL; use IS (NOT) NULL [OK]

Common Mistakes:

• Using != NULL or = NULL
• Adding quotes around NULL
• Assuming != NULL works like other values

5. You have a table orders with columns order_id, customer_id, and shipped_date. Some orders are not shipped yet and have shipped_date as NULL. Which query lists all orders that have been shipped?

hard

A. SELECT * FROM orders WHERE shipped_date IS NOT NULL;

B. SELECT * FROM orders WHERE shipped_date IS NULL;

C. SELECT * FROM orders WHERE shipped_date != NULL;

D. SELECT * FROM orders WHERE shipped_date = NULL;

Solution

1. Step 1: Understand meaning of shipped_date NULL

   NULL means order not shipped yet, so shipped orders have non-NULL dates.

2. Step 2: Use correct syntax to find shipped orders

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

3. Final Answer:

   SELECT * FROM orders WHERE shipped_date IS NOT NULL; -> Option A

4. Quick Check:

   IS NOT NULL finds shipped orders = SELECT * FROM orders WHERE shipped_date IS NOT NULL; [OK]

Hint: IS NOT NULL finds rows with values, perfect for shipped orders [OK]

Common Mistakes:

• Using = NULL or != NULL which are invalid
• Using IS NULL to find shipped orders
• Confusing NULL with empty string