Difference between revisions of "IFNULL"

From SQLZOO
Jump to: navigation, search
(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 16:01, 16 July 2012

Compatibility
IFNULL(f1, f2)
EngineOKAlternative
ingresYesCOALESCE(f1, f2)
mysqlYesCOALESCE(f1, f2)
oracleNoCOALESCE(f1, f2)
postgresNoCOALESCE(f1, f2)
sqlserverNoCOALESCE(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

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense