Select a column if other column is null

Question

I need to select a field called ProgramID from a table and if the ProgramID is NULL then I need to select the value in the InterimProgramID from the same table and alias it as ProgramID.

How can I make a conditional SELECT statement to do this?

Solution 1

You need the ISNULL function.

SELECT ISNULL(a, b)

b gets selected if a is null.

Also, you can use the WHEN/THEN select option, lookup in BOL. Essentially: its c switch/case block meets SQL.

Solution 2

You can use either the ISNULL function or the COALESCE function. They both do pretty much the same thing, however ISNULL only takes two parameters and COALESCE takes multiple parameters (returning the first non-null it encounters). Both try the first param, then the second, (and COALESCE continues on)

DECLARE @IAMNULL VARCHAR
DECLARE @IAMNOTNULL VARCHAR
SET @IAMNOTNULL = 'NOT NULL'

SELECT ISNULL(@IAMNULL, @IAMNOTNULL)
--Output: 'NOT NULL'

DECLARE @IAMNULLALSO VARCHAR

SELECT COALESCE(@IAMNULL, @IAMNULLALSO, @IAMNOTNULL)
--Output: 'NOT NULL'

Solution 3

  select COALESCE ( ProgramID , InterimProgramID ) as 'ProgramID' 

Solution 4

There is also:

Select NVL(Column_A, Column_B) From 'schema'.'table_name'

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

https://www.1keydata.com/sql/sql-nvl.html

Solution 5

SELECT ProgramID
  FROM a_table
 WHERE ProgramID IS NOT NULL
UNION
SELECT InterimProgramID AS ProgramID
  FROM a_table
 WHERE ProgramID IS NULL;

View more solutions

Comments

[dominicbri7]

This question is about SQL Server and IFNULL does not exist with that DBMS, only ISNULL

[giorgio79]

MySQL equivalent is IFNULL dev.mysql.com/doc/refman/5.7/en/…

[Peter Aron Zentai]

and its a || b in javascript

[1_bug]

PostgreSQL equivalent is NULLIF postgresql.org/docs/current/…

[Peter Aron Zentai]

I mean the question is strictly tagged for tsql

[Vojtech Stas]

This solution seems to answer the question best

[Akaisteph7]

@PeterAronZentai That doesn't mean people won't find this if looking for this same problem for other SQL variants. And Postgres equivalent is actually COALESCE