Difference between revisions of "COALESCE"
From SQLZOO
| 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 33: | Line 33: | ||
<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> | ||
Revision as of 14:38, 16 July 2012
| 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(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
