Logical and Conditional Functions

MySQL provides three logical and conditional functions, IF(), IFNULL(), and CASE. Here's an overview of their syntax:

  • IF(expr1, expr2, expr3)

  • IFNULL(expr1, expr2)

  • CASE value WHEN [compare-value1] THEN result1 [WHEN [compare-value2] THEN result2 ...] [ELSE result3] END

IF() takes the format

IF(expr1, expr2, expr3)

If expr1 is true (not NULL or 0), the function returns expr2. If false, it returns expr3. For example, to test whether there are any items in a table products:

SELECT IF(COUNT(product_id), "yes", "none") FROM products

Beware that if expr1 is a floating-point number that evaluates to less than one but not zero, it will be rounded to zero and thus equate to false.

Note that you may want to nest IF() ...

Get Sams Teach Yourself MySQL in 21 Days, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial