Archives

The List

« Tools for Laying out Website Navigation? | Main | MySQL errno 150 »

IFNULL :: MySQL Function of the Day

By Carsten | April 8, 2008

Function name: IFNULL
Aliases: -
Function type: Control Flow function
Purpose: Test whether an expression evaluates to NULL

Description: IFNULL(test, other_value) returns the value of test if test (evaluates to something which) is not NULL. If test is null, other_value is returned.

I use IFNULL fairly extensively in two cases. The first is when computing sums or averages in LEFT JOIN expressions, where the right-side table may not have any records present:

SELECT group_name, IFNULL(SUM(member_id), 0) AS members
FROM groups LEFT JOIN group_members USING (group_id)
GROUP BY group_name

The other is when an division may evaluate to NULL because the denomintor is 0 (in this instance, a particular exam may not have been taken by anyone yet):

SELECT exam_name, pass, fail,
       IFNULL(pass/(pass+fail), "N/A") as passrate
FROM exams

As shown in the last example, other_value may be of any type (including NULL, even if that doesn’t seem very useful ;-) ).

MySQL manual entry on IFNULL()

MySQL Function of the Day is a small series of concise information regarding most of the functions and operators available in MySQL. Inspired by PHPs funcaday, it’s meant to provide a daily dose of something in-between “Aha!” and “Ho-hum, I knew that already”, depending on your level of experience with MySQL. You can access the entire series with your browser here or pick up the RSS feed here.

Topics: MySQL Function of the day |

2 Responses to “IFNULL :: MySQL Function of the Day”

  1. Bertrand Gorge Says:
    April 8th, 2008 at 10:47 am

    IFNULL is called ISNULL on MS SQL, so if you want to be a bit portable, you should really use the function COALESCE, which works the same (when used with two parameters) and is more ANSI.

  2. Carsten Says:
    April 9th, 2008 at 8:04 am

    @Bertrand: Excellent comment, thanks!

Comments