COALESCE

Compatibility

COALESCE(f1, f2)
Engine	OK	Alternative
ingres	Yes
mysql	Yes	IFNULL(f1, f2)
oracle	Yes	NVL(f1, f2)
postgres	Yes
sqlserver	Yes

COALESCE

COALESCE takes any number of arguments and returns the first value that is not null.

  COALESCE(x,y,z) = x if x is not NULL
  COALESCE(x,y,z) = y if x is NULL and y is not NULL
  COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
  COALESCE(x,y,z) = NULL if x and y and z are all NULL

schema:gisq

COALESCE 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
      ,COALESCE(party,'None') AS aff
  FROM gisq.msp WHERE name LIKE 'C%'

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

See also

• CASE function
• NULLIF function
• NVL function