NULLIF function behavior in PostgreSQL - Mini Project: Build & Apply

Understanding NULLIF Function Behavior in PostgreSQL

📖 Scenario: You are managing a small online store database. Sometimes, the discount applied to a product is the same as the product price, which means the final price should be considered as NULL to indicate no effective price.

🎯 Goal: Build a simple query using the NULLIF function to return NULL when the discount equals the product price, otherwise return the product price.

📋 What You'll Learn

Create a table called products with columns product_id (integer), product_name (text), price (numeric), and discount (numeric).

Insert exactly three rows into products with the following data: (1, 'Pen', 10, 5), (2, 'Notebook', 20, 20), (3, 'Eraser', 5, 0).

Write a SELECT query that uses NULLIF to return final_price which is NULL if price equals discount, otherwise returns price.

Ensure the query returns product_id, product_name, and final_price.

💡 Why This Matters

🌍 Real World

NULLIF is useful in databases to handle cases where certain values should be treated as missing or undefined, such as when discounts equal prices.

💼 Career

Understanding NULLIF helps in writing clean SQL queries for data cleaning, reporting, and business logic implementation in real-world database jobs.

1

Create the products table and insert data

Create a table called products with columns product_id as integer, product_name as text, price as numeric, and discount as numeric. Then insert these exact rows: (1, 'Pen', 10, 5), (2, 'Notebook', 20, 20), and (3, 'Eraser', 5, 0).

PostgreSQL

-- Create the products table and insert the three rows below
-- Your code here

Hint

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

2

Add a SELECT query skeleton

Write a SELECT query that selects product_id and product_name from the products table. Do not add any other columns yet.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC,
    discount NUMERIC
);

INSERT INTO products (product_id, product_name, price, discount) VALUES
(1, 'Pen', 10, 5),
(2, 'Notebook', 20, 20),
(3, 'Eraser', 5, 0);

-- Write a SELECT query to get product_id and product_name from products
-- Your code here

Hint

Use SELECT product_id, product_name FROM products to get these columns.

3

Use NULLIF to calculate final_price

Modify the SELECT query to add a column called final_price that uses NULLIF(price, discount) to return NULL when price equals discount, otherwise returns price.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC,
    discount NUMERIC
);

INSERT INTO products (product_id, product_name, price, discount) VALUES
(1, 'Pen', 10, 5),
(2, 'Notebook', 20, 20),
(3, 'Eraser', 5, 0);

-- Add final_price column using NULLIF(price, discount) in the SELECT query
-- Your code here

Hint

Use NULLIF(price, discount) AS final_price in the SELECT clause.

4

Complete the query with ordering

Add an ORDER BY product_id clause at the end of the SELECT query to sort the results by product_id.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC,
    discount NUMERIC
);

INSERT INTO products (product_id, product_name, price, discount) VALUES
(1, 'Pen', 10, 5),
(2, 'Notebook', 20, 20),
(3, 'Eraser', 5, 0);

SELECT product_id, product_name, NULLIF(price, discount) AS final_price FROM products
-- Add ORDER BY product_id here
-- Your code here

Hint

Use ORDER BY product_id at the end of the query.

Practice

1. What does the NULLIF function return when both its arguments are equal?

easy

A. It returns an error.

B. It returns the second argument.

C. It returns the first argument.

D. It returns NULL.

Solution

1. Step 1: Understand the purpose of NULLIF

   The NULLIF function compares two values and returns NULL if they are equal.

2. Step 2: Apply the function behavior

   If both arguments are the same, NULLIF returns NULL instead of any value.

3. Final Answer:

   It returns NULL. -> Option D

4. Quick Check:

   Equal values = NULL [OK]

Hint: NULLIF returns NULL only when arguments match exactly [OK]

Common Mistakes:

• Thinking it returns the second argument when equal
• Assuming it always returns the first argument
• Believing it throws an error on equal values

2. Which of the following is the correct syntax to use NULLIF in PostgreSQL?

easy

A. NULLIF(value1; value2)

B. NULLIF value1, value2

C. NULLIF(value1, value2)

D. NULLIF[value1, value2]

Solution

1. Step 1: Recall the function syntax

   NULLIF is a standard SQL function that takes two arguments inside parentheses separated by a comma.

2. Step 2: Identify the correct syntax

   Only NULLIF(value1, value2) uses parentheses and comma correctly.

3. Final Answer:

   NULLIF(value1, value2) -> Option C

4. Quick Check:

   Function syntax = parentheses + comma [OK]

Hint: Use parentheses and comma for SQL functions [OK]

Common Mistakes:

• Omitting parentheses
• Using semicolon instead of comma
• Using square brackets instead of parentheses

3. What is the result of this query?

SELECT NULLIF(5, 5) AS result1, NULLIF(7, 5) AS result2;

medium

A. result1 = NULL, result2 = 7

B. result1 = 5, result2 = 7

C. result1 = NULL, result2 = NULL

D. result1 = 5, result2 = NULL

Solution

1. Step 1: Evaluate NULLIF(5, 5)

   Since both arguments are equal, NULLIF(5, 5) returns NULL.

2. Step 2: Evaluate NULLIF(7, 5)

   Arguments differ, so NULLIF(7, 5) returns the first argument, 7.

3. Final Answer:

   result1 = NULL, result2 = 7 -> Option A

4. Quick Check:

   Equal args = NULL, different args = first value [OK]

Hint: NULLIF returns NULL if equal, else first value [OK]

Common Mistakes:

• Assuming NULLIF always returns first argument
• Confusing which argument is returned when different
• Expecting both results to be NULL

4. Identify the error in this query:

SELECT NULLIF(10 10) AS result;

medium

A. Missing comma between arguments

B. NULLIF cannot take numeric arguments

C. NULLIF requires three arguments

D. Using wrong function name

Solution

1. Step 1: Check function argument syntax

   Arguments in NULLIF must be separated by a comma.

2. Step 2: Identify the missing comma

   The query has NULLIF(10 10) missing a comma between 10 and 10.

3. Final Answer:

   Missing comma between arguments -> Option A

4. Quick Check:

   Arguments need comma separator [OK]

Hint: Check commas between function arguments [OK]

Common Mistakes:

• Forgetting commas between arguments
• Thinking NULLIF needs more than two arguments
• Assuming numeric arguments are invalid

5. You want to avoid division by zero errors in this query:

SELECT value / divisor FROM numbers;

How can you use NULLIF to prevent errors when divisor is zero?

hard

A. SELECT NULLIF(value, 0) / divisor FROM numbers;

B. SELECT value / NULLIF(divisor, 0) FROM numbers;

C. SELECT value / divisor WHERE divisor != 0;

D. SELECT value / divisor NULLIF divisor, 0 FROM numbers;

Solution

1. Step 1: Understand division by zero problem

   Dividing by zero causes an error. We want to replace zero divisor with NULL to avoid error.

2. Step 2: Use NULLIF to replace zero divisor

   NULLIF(divisor, 0) returns NULL if divisor is zero, preventing division by zero error.

3. Step 3: Check options

   SELECT value / NULLIF(divisor, 0) FROM numbers; correctly uses value / NULLIF(divisor, 0). SELECT NULLIF(value, 0) / divisor FROM numbers; replaces value, not divisor. SELECT value / divisor WHERE divisor != 0; filters rows but does not use NULLIF. SELECT value / divisor NULLIF divisor, 0 FROM numbers; has syntax error.

4. Final Answer:

   SELECT value / NULLIF(divisor, 0) FROM numbers; -> Option B

5. Quick Check:

   Replace zero divisor with NULL using NULLIF [OK]

Hint: Use NULLIF(divisor, 0) to avoid division by zero [OK]

Common Mistakes:

• Replacing numerator instead of divisor
• Filtering rows instead of using NULLIF
• Writing invalid syntax with NULLIF