COALESCE and NULLIF as CASE shortcuts in SQL - Mini Project: Build & Apply

Using COALESCE and NULLIF as CASE Shortcuts

📖 Scenario: You are managing a small customer database for a local store. Some customers have missing phone numbers or prefer to use a secondary contact number. You want to write queries that handle these cases gracefully.

🎯 Goal: Build SQL queries that use COALESCE and NULLIF to simplify CASE expressions for handling missing or default values in customer contact information.

📋 What You'll Learn

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

Insert specific customer data with some NULL and empty string values in phone columns.

Write a query using COALESCE to select the first available phone number for each customer.

Write a query using NULLIF to treat empty strings as NULL in phone numbers.

💡 Why This Matters

🌍 Real World

Handling missing or default values in databases is common in real-world applications like customer management systems.

💼 Career

Knowing how to use COALESCE and NULLIF helps database developers and analysts write cleaner, more efficient queries.

1

Create the customers table and insert data

Create a table called customers with columns id (integer), name (text), phone_primary (text), and phone_secondary (text). Then insert these exact rows: (1, 'Alice', '123-4567', NULL), (2, 'Bob', NULL, '987-6543'), (3, 'Charlie', '', '555-0000'), (4, 'Diana', NULL, NULL).

SQL

-- Create the customers table and insert the specified rows
-- Your code here

Hint

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

2

Add a query to select the first available phone number using COALESCE

Write a SQL query that selects id, name, and a new column contact_phone which uses COALESCE to return the first non-NULL value between phone_primary and phone_secondary from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  phone_primary TEXT,
  phone_secondary TEXT
);

INSERT INTO customers (id, name, phone_primary, phone_secondary) VALUES
  (1, 'Alice', '123-4567', NULL),
  (2, 'Bob', NULL, '987-6543'),
  (3, 'Charlie', '', '555-0000'),
  (4, 'Diana', NULL, NULL);

-- Write a SELECT query using COALESCE to get contact_phone
-- Your code here

Hint

Use COALESCE(phone_primary, phone_secondary) to get the first phone number that is not NULL.

3

Use NULLIF to treat empty strings as NULL in phone_primary

Modify the previous query to use NULLIF on phone_primary so that empty strings ('') are treated as NULL. Then use COALESCE to select the first non-NULL phone number between the modified phone_primary and phone_secondary.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  phone_primary TEXT,
  phone_secondary TEXT
);

INSERT INTO customers (id, name, phone_primary, phone_secondary) VALUES
  (1, 'Alice', '123-4567', NULL),
  (2, 'Bob', NULL, '987-6543'),
  (3, 'Charlie', '', '555-0000'),
  (4, 'Diana', NULL, NULL);

-- Modify the SELECT query to use NULLIF on phone_primary
-- Your code here

Hint

Use NULLIF(phone_primary, '') to convert empty strings to NULL before using COALESCE.

4

Complete the query with ordering by customer name

Add an ORDER BY clause to the previous query to sort the results by name in ascending order.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  phone_primary TEXT,
  phone_secondary TEXT
);

INSERT INTO customers (id, name, phone_primary, phone_secondary) VALUES
  (1, 'Alice', '123-4567', NULL),
  (2, 'Bob', NULL, '987-6543'),
  (3, 'Charlie', '', '555-0000'),
  (4, 'Diana', NULL, NULL);

SELECT id, name, COALESCE(NULLIF(phone_primary, ''), phone_secondary) AS contact_phone FROM customers
-- Add ORDER BY name ascending
-- Your code here

Hint

Use ORDER BY name ASC to sort the results by customer name.

Practice

1. What does the SQL function COALESCE do?

easy

A. Returns the first non-NULL value from its arguments

B. Returns NULL if two values are equal

C. Always returns the last value in the list

D. Converts NULL values to zero

Solution

1. Step 1: Understand COALESCE behavior

   COALESCE checks each argument in order and returns the first one that is not NULL.

2. Step 2: Compare with other options

   NULLIF returns NULL if two values are equal, which is different. COALESCE does not convert NULL to zero automatically.

3. Final Answer:

   Returns the first non-NULL value from its arguments -> Option A

4. Quick Check:

   COALESCE = first non-NULL [OK]

Hint: COALESCE picks first non-NULL value in order [OK]

Common Mistakes:

• Confusing COALESCE with NULLIF
• Thinking COALESCE converts NULL to zero
• Assuming COALESCE returns last value

2. Which of the following is the correct syntax for using NULLIF in SQL?

easy

A. NULLIF(value1, value2)

B. NULLIF(value1 = value2)

C. NULLIF(value1; value2)

D. NULLIF(value1, value2, value3)

Solution

1. Step 1: Recall NULLIF syntax

   NULLIF takes exactly two arguments separated by a comma: NULLIF(value1, value2).

2. Step 2: Eliminate incorrect options

   NULLIF(value1 = value2) uses an equality inside parentheses, which is invalid syntax. NULLIF(value1; value2) uses a semicolon instead of a comma. NULLIF(value1, value2, value3) has three arguments, which is not allowed.

3. Final Answer:

   NULLIF(value1, value2) -> Option A

4. Quick Check:

   NULLIF syntax = NULLIF(val1, val2) [OK]

Hint: NULLIF takes exactly two comma-separated values [OK]

Common Mistakes:

• Using semicolon instead of comma
• Passing more than two arguments
• Using an expression inside NULLIF parentheses

3. Given the table Employees with columns id, bonus (nullable), what is the result of this query?

SELECT id, COALESCE(bonus, 100) AS bonus_amount FROM Employees WHERE id = 1;

Assuming the row with id=1 has bonus = NULL.

medium

A. No rows returned

B. [{"id": 1, "bonus_amount": 100}]

C. [{ "id": 1, "bonus_amount": 0 }]

D. [{ "id": 1, "bonus_amount": NULL }]

Solution

1. Step 1: Understand COALESCE with NULL bonus

   Since bonus is NULL for id=1, COALESCE returns the second argument, 100.

2. Step 2: Check query output

   The query selects id=1 and bonus_amount as 100, so the result is [{"id": 1, "bonus_amount": 100}].

3. Final Answer:

   [{"id": 1, "bonus_amount": 100}] -> Option B

4. Quick Check:

   COALESCE(NULL, 100) = 100 [OK]

Hint: COALESCE returns first non-NULL, here 100 [OK]

Common Mistakes:

• Assuming NULL is returned instead of 100
• Confusing COALESCE with NULLIF
• Expecting zero instead of 100

4. Identify the error in this SQL snippet:

SELECT id, NULLIF(bonus) AS result FROM Employees;

medium

A. Missing WHERE clause

B. NULLIF cannot be used in SELECT clause

C. NULLIF requires two arguments, only one given

D. NULLIF returns wrong data type

Solution

1. Step 1: Check NULLIF function usage

   NULLIF requires exactly two arguments, but only one (bonus) is provided here.

2. Step 2: Validate other options

   NULLIF can be used in SELECT. WHERE clause is optional. NULLIF does not cause data type errors by itself.

3. Final Answer:

   NULLIF requires two arguments, only one given -> Option C

4. Quick Check:

   NULLIF needs two values [OK]

Hint: NULLIF always needs two arguments [OK]

Common Mistakes:

• Using NULLIF with one argument
• Thinking WHERE clause is mandatory
• Assuming NULLIF changes data type

5. You want to replace a value with NULL if it equals zero, otherwise keep it as is. Which SQL expression using NULLIF achieves this?

hard

A. CASE WHEN value = 0 THEN value ELSE NULL END

B. COALESCE(value, 0)

C. NULLIF(0, value)

D. NULLIF(value, 0)

Solution

1. Step 1: Understand NULLIF behavior

   NULLIF(value, 0) returns NULL if value equals 0, else returns value unchanged.

2. Step 2: Compare other options

   COALESCE returns first non-NULL, not conditional replacement. CASE in CASE WHEN value = 0 THEN value ELSE NULL END returns value when zero, opposite of requirement. NULLIF(0, value) returns NULL if 0 equals value, which is reversed logic.

3. Final Answer:

   NULLIF(value, 0) -> Option D

4. Quick Check:

   NULLIF(value, 0) replaces zero with NULL [OK]

Hint: NULLIF(value, 0) returns NULL if value is zero [OK]

Common Mistakes:

• Using COALESCE instead of NULLIF
• Reversing argument order in NULLIF
• Writing CASE with wrong THEN/ELSE logic