Posted on 05/02/2009. By Pete Otaqui.
MySQL’s documentation is OK, but it’s examples are sometimes quite poor.
I have a particular setup where values across two tables – a course table and a schedule table which relates to it – can be effectively “overridden”. The idea is that for any given course, one can set “default” values, and then these can be overridden every time a course is actually scheduled to happen.
The courses table looks something like this:
COURSES
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
title VARCHAR(255) NOT NULL
subtitle VARCHAR(255) NOT NULL
description TEXT NOT NULL
SCHEDULES
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
course_id INT(11) NOT NULL
subtitle VARCHAR(255) NULL
description TEXT NULL
Hopefully you can see what I would want during a SELECT – to get the values from the schedules table if they aren’t null, or otherwise get the value from the courses table if they are.
This is achieved with the IFNULL built in function in MySQL. IFNULL takes two arguments, and returns the first argument if it (the first argument that is) is not null, or the second argument if it is. The actual SQL for my example looks like this:
SELECT courses.title, IFNULL(schedules.subtitle,courses.subtitle) AS subtitle, IFNULL(schedules.description,courses.description) AS description FROM schedules LEFT JOIN courses ON schedule.course_id=course.id;
This will give you the “subtitle” and “description” values from the linked table (schedules) if they exist, or the default values from the original table (courses) if they don’t.