Difference between revisions of "IFNULL"
From SQLZOO
(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...") |
|||
| Line 30: | Line 30: | ||
,IFNULL(party,'None') AS aff | ,IFNULL(party,'None') AS aff | ||
FROM msp WHERE name LIKE 'C%' | FROM msp WHERE name LIKE 'C%' | ||
| + | </source> | ||
| + | <source lang='sql' class='def e-oracle'> | ||
| + | SELECT name, party | ||
| + | ,COALESCE(party,'None') AS aff | ||
| + | FROM gisq.msp WHERE name LIKE 'C%' | ||
</source> | </source> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
Revision as of 15:01, 16 July 2012
| IFNULL(f1, f2) | ||
|---|---|---|
| Engine | OK | Alternative |
| ingres | Yes | COALESCE(f1, f2) |
| mysql | Yes | COALESCE(f1, f2) |
| oracle | No | COALESCE(f1, f2) |
| postgres | No | COALESCE(f1, f2) |
| sqlserver | No | COALESCE(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 gisq.msp WHERE name LIKE 'C%'
SELECT name, party ,COALESCE(party,'None') AS aff FROM msp WHERE name LIKE 'C%'
See also
