Coalesce Function in TSQL

sql

null-handling

sql-queries

ansi-sql-standard

byAlex Kataev·Dec 15, 2024

Just want the quick 'n dirty? COALESCE is your knight in shining armor when it comes to fighting off those pesky NULL values. It'll return the first non-NULL value in its parameters.

Example:

-- Check this out. No magic spells, just straightforward stuff. 
SELECT COALESCE(column1, column2, 'Plan B') AS safety_net FROM my_table;

Here, users receive the value of column1, unless it's NULL, in which case column2 steps up. If both are NULL, you've still got that trusty 'Plan B'.

All Aboard The `COALESCE` Train

Dealing with Diverse Data Types

Unlike a fussy eater, COALESCE isn’t bothered by different data types in its diet. It handles them by auto-casting them according to standard T-SQL data type precedence rules.

Migrating from CASE Statements

Complex CASE statements giving you headaches? COALESCE can act like an aspirin, simplifying your queries and enhancing readability. Say goodbye to painful NULL handling and hello to cleaner result sets.

Grappling with SQL Flavors

If you're SQL bilingual (or more), COALESCE, an ANSI-SQL standard function, ensures compatibility across various SQL dialects. Sorry, ISNULL, but you're just not as worldly.

Where, Oh WHERE, is my Data?

Our trusty friend COALESCE can also filter out NULL values in WHERE clauses. Less hide and seek, more sought and found.

Visual Experience

Let's visualise COALESCE with a Pantry Analogy 🍴:

Ingredient_A 🥩	Ingredient_B 🥦	Ingredient_C 🍄	Cook's Secret 🧂
Available	Unavailable	Unavailable	Always there

🍲 => COALESCE picks Ingredient_A 🥩 to star in the recipe!

You never go hungry with COALESCE. It ensures every dish is served with flavor! 🍽️👌

Confronting Complex Cases

Multiple Rascals (NULLs)

When you have several columns that could be NULL, COALESCE helps you pin down the first non-null value.

-- It's like a game of 'Where's Wally?' but for NULLs.
SELECT COALESCE(col1, col2, col3, ...) FROM my_table_of_wonders;

Setting Up Defaults

Using COALESCE, you can default to an alternative value when other options fail, to ensure there’s always an answer.

-- I love it when a plan comes together!
SELECT COALESCE(col1, col2, 'Fallback plan') FROM my_table_of_plans;

Filling in the Blanks

Instead of handing over a NULL to your end-users, let COALESCE fill in the gaps with a friendly message or default value.

-- This makes more sense than 'reply all' to a null email address.
SELECT FirstName, COALESCE(EmailAddress, 'No email on file') FROM Customers;

explain-codes / Sql / Coalesce Function in TSQL

Linked

Select a column if other column is NULL



Check if a parameter is null or empty in a stored procedure



Sql Query If Parameter Is Null Select All



Replace Default Null Values Returned From Left Outer Join



If-then-else statements in postgresql



Sqlite syntax for If Else condition



Standard use of 'Z' instead of NULL to represent missing data?



All Aboard The Train Visual Experience