IS NULL and IS NOT NULL in MySQL - Mini Project: Build & Apply

Filtering NULL Values with IS NULL and IS NOT NULL in MySQL

📖 Scenario: You are managing a small online store database. Some products have missing information in the description field because the details are not yet available.You want to learn how to find products that have missing descriptions and those that have descriptions filled in.

🎯 Goal: Build SQL queries to select products with NULL descriptions and products with non-NULL descriptions using IS NULL and IS NOT NULL.

📋 What You'll Learn

Create a table called products with columns id, name, and description.

Insert 5 products with some description values set to NULL.

Write a query to select products where description IS NULL.

Write a query to select products where description IS NOT NULL.

💡 Why This Matters

🌍 Real World

Handling missing data is common in databases. Knowing how to find NULL values helps keep data clean and accurate.

💼 Career

Database administrators and developers often need to filter and manage NULL values to ensure correct data processing and reporting.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (varchar 50), and description (varchar 100). Insert these 5 products exactly:
(1, 'Laptop', 'Portable computer'), (2, 'Mouse', NULL), (3, 'Keyboard', 'Mechanical keyboard'), (4, 'Monitor', NULL), (5, 'Printer', 'Color printer').

MySQL

-- Create the products table and insert 5 products with some NULL descriptions
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows. Use NULL for missing descriptions.

2

Set up a query to find products with NULL descriptions

Write a SQL query to select all columns from products where the description is NULL. Use the condition description IS NULL.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  description VARCHAR(100)
);

INSERT INTO products (id, name, description) VALUES
(1, 'Laptop', 'Portable computer'),
(2, 'Mouse', NULL),
(3, 'Keyboard', 'Mechanical keyboard'),
(4, 'Monitor', NULL),
(5, 'Printer', 'Color printer');

-- Write a query to select products where description IS NULL
-- Your code here

Need a hint?

Use SELECT * FROM products WHERE description IS NULL; to find rows with missing descriptions.

3

Set up a query to find products with non-NULL descriptions

Write a SQL query to select all columns from products where the description is NOT NULL. Use the condition description IS NOT NULL.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  description VARCHAR(100)
);

INSERT INTO products (id, name, description) VALUES
(1, 'Laptop', 'Portable computer'),
(2, 'Mouse', NULL),
(3, 'Keyboard', 'Mechanical keyboard'),
(4, 'Monitor', NULL),
(5, 'Printer', 'Color printer');

SELECT * FROM products WHERE description IS NULL;

-- Write a query to select products where description IS NOT NULL
-- Your code here

Need a hint?

Use SELECT * FROM products WHERE description IS NOT NULL; to find rows with descriptions filled in.

4

Complete the project with comments explaining the queries

Add comments above each SELECT query explaining what it does: one comment for the query selecting products with NULL descriptions and one comment for the query selecting products with non-NULL descriptions.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  description VARCHAR(100)
);

INSERT INTO products (id, name, description) VALUES
(1, 'Laptop', 'Portable computer'),
(2, 'Mouse', NULL),
(3, 'Keyboard', 'Mechanical keyboard'),
(4, 'Monitor', NULL),
(5, 'Printer', 'Color printer');

-- Your comment here
SELECT * FROM products WHERE description IS NULL;

-- Your comment here
SELECT * FROM products WHERE description IS NOT NULL;

Need a hint?

Use -- to add comments above each query explaining its purpose.