Select a column if other column is NULL

sql

null-handling

sql-functions

conditional-statements

byNikita Barsukov·Oct 28, 2024

Let's quickly address the problem by using COALESCE function:

SELECT COALESCE(nullable_column, alternate_column) AS output_desired
FROM table_name

COALESCE simply grabs the first non-NULL value. Thus, if nullable_column is NULL, alternate_column will be used.

For more intricate logic, you would use a CASE expression:

SELECT CASE 
  WHEN nullable_column IS NULL THEN alternate_column
  ELSE nullable_column
END AS output_desired
FROM table_name

Within CASE, nullable_column is checked for NULL. If it's true, alternate_column is taken, else nullable_column is picked.

Digging into NULL handling tactics

We have two major players in the battle against NULLs: COALESCE and ISNULL. COALESCE examines more than one parameter, selecting the first non-null one. ISNULL, confined to SQL Server, can handle only two parameters. Oracle database has a function NVL with similar purpose. So, COALESCE is generally hailed due its superior power and flexibility.

Addressing twisted conditions and aliasing

More complex scenarios require more logical weaponry. That's where CASE comes into play. It behaves like a switch/case block delivering total control over the logic.

Suppose you want to alias "InterimProgramID" as "ProgramID" if "ProgramID" is null:

-- Sometimes, "ProgramID" is a no-show. No worries, "InterimProgramID" got our back!
SELECT CASE 
  WHEN ProgramID IS NULL THEN InterimProgramID 
  ELSE ProgramID 
END AS ProgramID
FROM Programs;

This conditional retrieval ensures a harmonious output in column names, a vital characteristic for later processing or UI representation.

Wrestling with an army of NULLs

Sometimes, multiple NULL columns tell you not to get the door. COALESCE is your best buddy here, it checks each column systematically until it finds a non-null value. You got to love COALESCE.

-- When "ProgramID" bails, "InterimProgramID" steps in. If that falls off, "DefaultProgramID" saves the day!
SELECT COALESCE(ProgramID, InterimProgramID, DefaultProgramID) AS ProgramID
FROM Programs;

Here, it tries to select "ProgramID", then "InterimProgramID", and finally "DefaultProgramID" if the first two fail the audition.

explain-codes / Sql / Select a column if other column is NULL

Linked

Select one column if the other is null



If-then-else statements in postgresql



Select column, if blank select from another



Coalesce Function in TSQL



What SQL coding standard do you follow?