IFNULL

From SQLZOO
Revision as of 12:02, 16 July 2012 by Connor (Talk | contribs) (Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>IFNULL(f1, f2)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'>'''OK...")

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Compatibility
IFNULL(f1, f2)
EngineOKAlternative
ingresYesCOALESCE(f1, f2)
mysqlYesCOALESCE(f1, f2)
oracleNoCOALESCE(f1, f2)
postgresNoCOALESCE(f1, f2)
sqlserverNoCOALESCE(f1, f2)

IFNULL

IFNULL takes two arguments and returns the first value that is not null.

    IFNULL(x,y) = x if x is not NULL
  IFNULL(x,y) = y if x is NULL

IFNULL can be useful when you want to replace a NULL value with some other value. In this example you show the name of the party for each MSP that has a party. For the MSP with no party (such as Canavan, Dennis) you show the string None.

SELECT name, party
      ,IFNULL(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'
SELECT name, party
      ,IFNULL(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'
SELECT name, party
      ,COALESCE(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'

See also