Select a column if other column is NULL
Let's quickly address the problem by using COALESCE
function:
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:
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:
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
.
Here, it tries to select "ProgramID", then "InterimProgramID", and finally "DefaultProgramID" if the first two fail the audition.
Was this article helpful?