Explain Codes LogoExplain Codes Logo

Select a column if other column is NULL

sql
null-handling
sql-functions
conditional-statements
Nikita BarsukovbyNikita Barsukov·Oct 28, 2024
TLDR

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.