Difference between revisions of "COALESCE"

From SQLZOO
Jump to: navigation, search
 
(3 intermediate revisions by 2 users not shown)
Line 4: Line 4:
 
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
 
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
 
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
<tr><td align='left'>mysql</td><td>Yes</td><td>[[IFNULL(f1, f2)]]</td></tr>
+
<tr><td align='left'>mysql</td><td>Yes</td><td>[[IFNULL |IFNULL(f1, f2)]]</td></tr>
<tr><td align='left'>oracle</td><td>Yes</td><td>[[NVL(f1, f2)]]</td></tr>
+
<tr><td align='left'>oracle</td><td>Yes</td><td>[[NVL |NVL(f1, f2)]]</td></tr>
 
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
Line 15: Line 15:
 
   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
   COALESCE(a,y,z) = z if x and y are NULL but z 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
 
   COALESCE(x,y,z) = NULL if x and y and z are all NULL
 
</pre>
 
</pre>
  
<div class='qu'>
+
<div class='ht'>
 +
<div class=params>schema:gisq</div>
 
COALESCE can be useful when you want to replace a NULL value with some other value.  
 
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.  
 
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.   
 
For the MSP with no party (such as Canavan, Dennis) you show the string None.   
 +
<source lang='sql' class='def e-oracle'>
 +
SELECT name, party
 +
      ,COALESCE(party,'None') AS aff
 +
  FROM gisq.msp WHERE name LIKE 'C%'
 +
</source>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT name, party
 
SELECT name, party
Line 33: Line 39:
 
<ul>
 
<ul>
 
   <li>[[CASE |CASE function]]</li>
 
   <li>[[CASE |CASE function]]</li>
   <li>[[NULLIF function]]</li>
+
   <li>[[NULLIF |NULLIF function]]</li>
   <li>[[NVL function]]</li>
+
   <li>[[NVL |NVL function]]</li>
 
</ul>
 
</ul>
 +
 +
{{Languages}}

Latest revision as of 11:04, 1 October 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(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

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense