MySQLism: Use CASE instead of IF().
Drupal 7 will no longer be supported after January 5, 2025. Learn more and find resources for Drupal 7 sites
MySQL has a easy syntax that lets you execute IF/ELSE statements in SQL. For example:
SELECT c.subject, IF (u.uid, u.name, c.name) AS name FROM {comments} c INNER JOIN ....
In the example above, if the first argument of IF() is TRUE or NOT NULL or NOT FALSE then the second argument will populate the second column of that row. Otherwise, the third argument will. In other words:
IF (condition, then, else)
IF(), however, is a MySQLism and isn't ANSI compliant. instead, it is better to use CASE which allows for multiple outcomes rather than just one or the other:
SELECT CASE u.name
WHEN 'Josh' THEN 'male'
WHEN 'Sally' THEN 'female'
ELSE 'unknown' END as gender
FROM {users} u
In a case statement you can add as many cases as you want just like a PHP switch statement. While the syntax is bit longer you can do much more with it!
References:
http://dev.mysql.com/doc/refman/5.1/en/case-statement.html
http://www.postgresql.org/docs/8.3/static/functions-conditional.html
Help improve this page
You can:
- Log in, click Edit, and edit this page
- Log in, click Discuss, update the Page status value, and suggest an improvement
- Log in and create a Documentation issue with your suggestion