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

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

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

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

Hint

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

Practice

1. What does NULL represent in a database?

easy

A. False boolean

B. Zero value

C. Empty string

D. Missing or unknown data

Solution

1. Step 1: Understand the meaning of NULL

   NULL in databases means the data is missing or unknown, not zero or empty.

2. Step 2: Differentiate NULL from other values

   Zero, empty string, and false are actual values, while NULL means no value is stored.

3. Final Answer:

   Missing or unknown data -> Option D

4. Quick Check:

   NULL means missing or unknown data [OK]

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

Common Mistakes:

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

2. Which of the following is the correct way to check if a column col is NULL in SQL?

easy

A. WHERE col = NULL

B. WHERE col == NULL

C. WHERE col IS NULL

D. WHERE col <> NULL

Solution

1. Step 1: Recall SQL syntax for NULL checks

   In SQL, to check for NULL, you must use IS NULL because = NULL does not work.

2. Step 2: Eliminate incorrect syntax

   col = NULL and col == NULL are invalid for NULL checks; col <> NULL also does not work.

3. Final Answer:

   WHERE col IS NULL -> Option C

4. Quick Check:

   Use IS NULL to check NULL values [OK]

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

Common Mistakes:

• Using = NULL instead of IS NULL
• Using == NULL which is invalid in SQL
• Using <> NULL to check for NULL

3. Given a table users with a column age that contains some NULL values, what will this query return?

SELECT * FROM users WHERE age <> 30;

medium

A. All rows where age is not 30, including rows with NULL age

B. Only rows where age is not 30 and not NULL

C. Only rows where age is 30

D. No rows, because NULL causes error

Solution

1. Step 1: Understand comparison with NULL

   Comparisons like age <> 30 return UNKNOWN for NULL values, so those rows are excluded.

2. Step 2: Determine which rows are returned

   Only rows where age is not 30 and age is not NULL will be returned.

3. Final Answer:

   Only rows where age is not 30 and not NULL -> Option B

4. Quick Check:

   Comparisons with NULL exclude NULL rows [OK]

Hint: Comparisons exclude NULL rows, so NULLs are ignored [OK]

Common Mistakes:

• Assuming NULL rows are included
• Thinking NULL causes errors in comparisons
• Confusing <> with IS NOT NULL

4. You wrote this query to find rows where status is not NULL:

SELECT * FROM orders WHERE status <> NULL;

Why does it return no rows, and how to fix it?

medium

A. Comparison with NULL returns UNKNOWN; use status IS NOT NULL

B. Syntax error; use status != NULL

C. NULL is treated as zero; use status <> 0

D. Query is correct; no rows have status

Solution

1. Step 1: Understand NULL comparison behavior

   Comparing with NULL using <> returns UNKNOWN, so no rows match.

2. Step 2: Use correct syntax to check NOT NULL

   Use status IS NOT NULL to find rows where status is not NULL.

3. Final Answer:

   Comparison with NULL returns UNKNOWN; use status IS NOT NULL -> Option A

4. Quick Check:

   Use IS NOT NULL to check for non-NULL values [OK]

Hint: Use IS NOT NULL, not <> NULL, to find non-NULL rows [OK]

Common Mistakes:

• Using <> NULL instead of IS NOT NULL
• Assuming <> NULL works like != NULL
• Thinking NULL is zero or empty

5. You want to select all rows from products where discount is either NULL or greater than 10. Which query correctly returns these rows?

hard

A. SELECT * FROM products WHERE discount > 10 OR discount IS NULL;

B. SELECT * FROM products WHERE discount > 10 AND discount IS NULL;

C. SELECT * FROM products WHERE discount > 10 OR discount = NULL;

D. SELECT * FROM products WHERE discount > 10 AND discount = NULL;

Solution

1. Step 1: Understand NULL checks with OR condition

   To include NULL values, use IS NULL, not = NULL.

2. Step 2: Combine conditions correctly

   Use OR to select rows where discount is greater than 10 or discount is NULL.

3. Final Answer:

   SELECT * FROM products WHERE discount > 10 OR discount IS NULL; -> Option A

4. Quick Check:

   Use IS NULL with OR to include NULLs [OK]

Hint: Use OR with IS NULL to include NULL values in conditions [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using AND instead of OR with NULL checks
• Assuming NULL compares like normal values