Ifnull
The ifnull function enables you to specify a value other than a null that is returned to your application when a null is encountered. The ifnull function is specified as follows:
ifnull(v1,v2)
If the value of the first argument is not null, ifnull returns the value of the first argument. If the first argument evaluates to a null, ifnull returns the second argument.
The sum, avg, max, and min aggregate functions return 0 if the argument to the function evaluates to an empty set. To receive a specified value when the function evaluates to an empty set, use the ifnull function, as in this example:
ifnull(sum(employee.salary)/25, -1)
Ifnull returns the value of the expression "sum(employee.salary)/25" unless that expression is null. If that expression is null, the ifnull function returns –1.
If the arguments are of the same data type, the result is of that data type. If the two arguments are of different data types, they must be of comparable data types. For a description of comparable data types, see
Assignment.
When the arguments are of different but comparable data types, the DBMS Server uses the following rules to determine the data type of the result:
• The result type is always the higher of the two data types; the order of precedence of the data types is as follows:
date > money > float4 > float > i4 > i2 > i1
and
c > text > char > varchar
• The result length is taken from the longest value. For example,
ifnull (varchar (5), c10)
results in c10.
The result is nullable if either argument is nullable. The first argument is not required to be nullable, though in most applications it is nullable.