COALESCE and NULLIF as CASE shortcuts in SQL

Introduction

COALESCE and NULLIF help you write shorter and easier-to-read queries instead of using long CASE statements.

When you want to pick the first non-empty value from a list of columns.

When you want to return NULL if two values are the same, otherwise return the first value.

When you want to replace NULL values with a default value quickly.

When you want to avoid writing multiple WHEN conditions for simple checks.

Syntax

SQL

COALESCE(value1, value2, ..., valueN)
NULLIF(value1, value2)

COALESCE returns the first value that is not NULL from the list.

NULLIF returns NULL if the two values are equal; otherwise, it returns the first value.

Examples

This returns 'apple' because it is the first non-NULL value.

SQL

SELECT COALESCE(NULL, 'apple', 'banana');

This returns NULL because both values are equal.

SQL

SELECT NULLIF(5, 5);

This returns 5 because the values are different.

SQL

SELECT NULLIF(5, 3);

This returns 'orange' as it is the first non-NULL value.

SQL

SELECT COALESCE(NULL, NULL, 'orange');

Sample Program

This example shows how COALESCE replaces NULL names with 'unknown'. NULLIF returns NULL for color 'red', otherwise the color value.

SQL

CREATE TABLE fruits (id INT, name VARCHAR(20), color VARCHAR(20));
INSERT INTO fruits VALUES (1, NULL, 'red'), (2, 'apple', NULL), (3, NULL, NULL);

SELECT id,
       COALESCE(name, 'unknown') AS fruit_name,
       NULLIF(color, 'red') AS fruit_color
FROM fruits
ORDER BY id;

Important Notes

COALESCE can take any number of arguments, but it stops checking as soon as it finds a non-NULL value.

NULLIF is like a shortcut for a CASE statement that returns NULL when two values match.

Summary

COALESCE picks the first non-NULL value from a list.

NULLIF returns NULL if two values are equal, else returns the first value.

Both help simplify CASE statements for common NULL and equality checks.