NULLIF() for converting placeholder values to NULL

NULLIF() swaps a specific value for a true blank (NULL) whenever it finds an exact match, so placeholder stand-ins like 999 or "N/A" stop pretending to be real data.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Turning Retail Placeholder Values into NULL with NULLIF()

SELECT product_id, NULLIF(brand, 'N/A') AS clean_brand, category, NULLIF(price, 999) AS clean_price, NULLIF(stock_count, 999) AS clean_stock
FROM products

Result:

product_id	clean_brand	category	clean_price	clean_stock
P2001	NovaTech	Electronics	129.99	45
P2002		Clothing	34.5
P2003	GlowUp	Beauty		12
P2004	FreshBasket	Grocery	5.99	200
P2005		Fashion	89.0

Step by step

Run the query without NULLIF so you can see what the unfiltered values look like.

SELECT product_id, brand, category, NULLIF(price, 999) AS clean_price, NULLIF(stock_count, 999) AS clean_stock
FROM products;

NULLIF returns NULL when both arguments are equal; otherwise the first argument.

SELECT product_id, NULLIF(brand, 'N/A'), category, NULLIF(price, 999) AS clean_price, NULLIF(stock_count, 999) AS clean_stock
FROM products;

AS gives the NULLIF result a friendly column name so the output reads cleanly.

SELECT product_id, NULLIF(brand, 'N/A') AS clean_brand, category, NULLIF(price, 999) AS clean_price, NULLIF(stock_count, 999) AS clean_stock
FROM products;

Practice

Try each one in your head first, then open the answer.

1. Using customers, write a query that returns NULL whenever full_name is 0, otherwise returns full_name.

SELECT NULLIF(full_name, 0) FROM customers

2. Using products, write a query that returns NULL whenever price is 0, otherwise returns price.

SELECT NULLIF(price, 0) FROM products

3. Using orders, write a query that returns NULL whenever revenue is 0, otherwise returns revenue.

SELECT NULLIF(revenue, 0) FROM orders

4. Using inventory, write a query that returns NULL whenever stock_count is 0, otherwise returns stock_count.

SELECT NULLIF(stock_count, 0) FROM inventory

5. Using reviews, write a query that returns NULL whenever rating is 0, otherwise returns rating.

SELECT NULLIF(rating, 0) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed