Something I've rarely needed to use, but recently rediscovered is using nested inline MySQL IF statements in MySQL select queries to conditionally assign a value to a field alias. If that doesn't make sense, read on. It's pretty simple.

The Basic "IF"

IF functions in MySQL (when used inline, as opposed to IF in a stored procedure) is nothing more than a ternary statement. It takes three parts and has a very simple syntax:

IF(condition, value if true, value if false)

So for example, if you wanted to calculate the sales tax on an order, but only if the purchaser was in Pennsylvania:

SELECT IF(state = 'PA', order_total * 0.06, order_total * 0.0) AS sales_tax
FROM order

Nesting "IF"

The example that I recently encountered was needing to conditionally determine a person's location, in order to sort results by location.  The problem is that within this application, the business rules are such that a person can have:

• one or none of (home USA address or home international address)
• one or none of (work USA address or work international address)

This application separates US and international addresses to better enforce data constraints and validation.  So for example, I can't select all people and hope to sort just by home state, because home state will be null for those people who either don't live in the US, or didn't provide a home address at all.  For this application, a person's generic location for reporting is the first non-null result found in the following order:

• home USA address
• home international address
• work USA address
• work international address

...which is practically pseudo-code for our query. I'll spare further details – I imagine you get the point and will understand the field names used here, but the nested IF we ended up using in the query is:

SELECT
IF(home_usa_state_abbr IS NOT NULL, home_usa_state_abbr,
   IF(home_int_territory IS NOT NULL, home_int_territory,
      IF(work_usa_state_abbr IS NOT NULL, work_usa_state_abbr,
         IF(work_int_territory IS NOT NULL, work_int_territory, 'zzz')
         )
      )
   ) AS location_sort
...
ORDER BY location_sort

Essentially, the first non-null value encountered is assigned to the alias "location_sort". Of note is the use of 'zzz' on line 5, which essentially relegates those people with no specified address to the bottom of the sort. Without this people with no address were originally listed first, which was undesirable.; (See stackoverflow's SQL how to make null values come last when sorting ascending for a different way to accomplish this when you're not already selecting for a sortable value.)