NULLIF()

NULLIF() swaps a specific value for NULL whenever that value appears - handy when a zero (or any placeholder) doesn't really mean zero, it means "not applicable.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Turning Placeholder Values into NULL with NULLIF()

SELECT product_id, brand, discount, NULLIF(discount, 0) AS adjusted_discount
FROM products

Result:

product_id	brand	discount	adjusted_discount
P2001	NovaTech	15.0	15.0
P2002	StyleCo	0
P2003	GlowUp	0
P2004	HomeNest	10.0	10.0
P2005	FreshMart	0

Step by step

Run the query without NULLIF so you can see what the unfiltered values look like.
```
SELECT product_id, brand, discount, discount
FROM products;
```
NULLIF returns NULL when both arguments are equal; otherwise the first argument.
```
SELECT product_id, brand, discount, NULLIF(discount, 0)
FROM products;
```

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

SELECT product_id, brand, discount, NULLIF(discount, 0) AS adjusted_discount
FROM products;

Practice

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

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

SELECT NULLIF(phone, 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