Difference between revisions of "NVL"

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 1: Line 1:
 
<table align='right' border='1'>
 
<table align='right' border='1'>
 
<caption>Compatibility</caption>
 
<caption>Compatibility</caption>
<tr><th colspan='3'>COALESCE(f1, f2)</th></tr>
+
<tr><th colspan='3'>NVL(f1, f2)</th></tr>
 
<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>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
<tr><td align='left'>mysql</td><td>Yes</td><td>[[IFNULL(f1, f2)]]</td></tr>
+
<tr><td align='left'>mysql</td><td>No</td><td>[[COALESCE |COALESCE(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>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
+
<tr><td align='left'>postgres</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
+
<tr><td align='left'>sqlserver</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
 
</table>
 
</table>
<h1>COALESCE</h1>
+
<h1>NVL</h1>
<p>COALESCE takes any number of arguments and returns the first value that is not null. </p>
+
<p>NVL takes two arguments and returns the first value that is not null.</p>
 
<p></p>  
 
<p></p>  
<pre style='width:85ex'>
+
<pre style='width:60ex'>
  COALESCE(x,y,z) = x if x is not NULL
+
  NVL(x,y) = x if x is not NULL
  COALESCE(x,y,z) = y if x is NULL and y is not NULL
+
  NVL(x,y) = y if x is 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
+
 
</pre>
 
</pre>
  
<div class='qu'>
+
<div class='ht'>
COALESCE can be useful when you want to replace a NULL value with some other value.  
+
NVL 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
 +
      ,NVL(party,'None') AS aff
 +
  FROM msp WHERE name LIKE 'C%'
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT name, party
 
SELECT name, party

Revision as of 11:19, 16 July 2012

Compatibility
NVL(f1, f2)
EngineOKAlternative
ingresNoCOALESCE(f1, f2)
mysqlNoCOALESCE(f1, f2)
oracleYesCOALESCE(f1, f2)
postgresNoCOALESCE(f1, f2)
sqlserverNoCOALESCE(f1, f2)

NVL

NVL takes two arguments and returns the first value that is not null.

   NVL(x,y) = x if x is not NULL
   NVL(x,y) = y if x is NULL

NVL 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
      ,NVL(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'
<SOURCE lang='sql' class='def'>
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