Something I've rarely needed to use, but recently rediscovered is using nested inline MySQL IF statements in select queries to conditionally assign a value. 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) are nothing more than ternary statements. They take three parts and have 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', price * 0.06, price * 0.0) AS 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)

... AND we need a query that selects all people ordered by location.  We don't have a single field to order by.  This application separates US and international addresses to better enforce data constraints and validation, so we end up with field names like home_us_state_abbr, or work_int_territory in various tables.

It's basically an inheritance pattern where all addresses share common fields, yet have additional fields dependent on the type of address (US or international). 

So, 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 is assumed to be the first non-null result found in the following list:

• 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 end of the result set. 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.)