Difference between revisions of "COALESCE"

From SQLZOO
Jump to: navigation, search
(Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>COALESCE(f1, f2)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'>'''...")
 
Line 12: Line 12:
 
<p>COALESCE takes any number of arguments and returns the first value that is not null. </p>
 
<p>COALESCE takes any number of arguments and returns the first value that is not null. </p>
 
<p></p>  
 
<p></p>  
<pre style='width:75ex'>
+
<pre style='width:85ex'>
 
   COALESCE(x,y,z) = x if x 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) = y if x is NULL and y is not NULL

Revision as of 11:13, 16 July 2012

Compatibility
COALESCE(f1, f2)
EngineOKAlternative
ingresYes
mysqlYesIFNULL(f1, f2)
oracleYesNVL(f1, f2)
postgresYes
sqlserverYes

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(a,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

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 msp WHERE name LIKE 'C%'

See also

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense