IFNULL and COALESCE in MySQL - Mini Project: Build & Apply

Using IFNULL and COALESCE in MySQL

📖 Scenario: You work for a small online store. Some customers have missing phone numbers in the database. You want to create a report that shows a phone number for each customer. If the phone number is missing, you want to show a default message instead.

🎯 Goal: Build a MySQL query that uses IFNULL and COALESCE functions to replace missing phone numbers with a default message.

📋 What You'll Learn

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

Insert 3 customers with exact data including one with a NULL phone number.

Write a query using IFNULL to show the phone number or 'No phone provided' if NULL.

Write a query using COALESCE to show the phone number or 'No phone provided' if NULL.

💡 Why This Matters

🌍 Real World

Handling missing data in customer contact information to improve reports and user experience.

💼 Career

Database developers and analysts often need to manage NULL values to ensure clean and meaningful data output.

1

Create the customers table and insert data

Create a table called customers with columns id (integer), name (varchar 50), and phone (varchar 20). Insert these exact rows: (1, 'Alice', '123-4567'), (2, 'Bob', NULL), (3, 'Charlie', '987-6543').

MySQL

-- Create the customers table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set the default message variable

Create a variable called @default_msg and set it to the string 'No phone provided'.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  phone VARCHAR(20)
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-4567'),
(2, 'Bob', NULL),
(3, 'Charlie', '987-6543');

-- Set the default message variable
-- Your code here

Need a hint?

Use SET to create a user variable in MySQL.

3

Write a query using IFNULL

Write a SELECT query that shows id, name, and the phone number using IFNULL(phone, @default_msg) as phone_number from the customers table.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  phone VARCHAR(20)
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-4567'),
(2, 'Bob', NULL),
(3, 'Charlie', '987-6543');

SET @default_msg = 'No phone provided';

-- Write the SELECT query using IFNULL
-- Your code here

Need a hint?

Use IFNULL(column, value) to replace NULLs in the query.

4

Write a query using COALESCE

Write a SELECT query that shows id, name, and the phone number using COALESCE(phone, @default_msg) as phone_number from the customers table.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  phone VARCHAR(20)
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-4567'),
(2, 'Bob', NULL),
(3, 'Charlie', '987-6543');

SET @default_msg = 'No phone provided';

SELECT id, name, IFNULL(phone, @default_msg) AS phone_number FROM customers;

-- Write the SELECT query using COALESCE
-- Your code here

Need a hint?

Use COALESCE(column, value) to replace NULLs in the query.