MySQL Conditional Statements

Brief overview of how I have been known to use (abuse) MySQL conditional statements to achieve a beneficial outcome.

IF

IF is a nice and simple conditional statement. I use if to convert boolean values to something that the user understands, to display meaningful values when using left/right joins when the default would have been null or in a more complicated example to run a different subquery depending on the type of record that I’m dealing with.

Making things human readable is

IF( genderVal=1,’Male’,’Female’) AS `gender`

Making left/right joins easier for humans to understand in reports

IF ( someValue IS NULL,’N/A’,someValue) AS `anotherValue`

Making a count If function as seen in spreadsheets

SUM(IF( someField >= 1,1,0 ))

Making a sum If function as seen in spreadsheets

SUM(IF( someField >= 1,someField,0 ))

Running a different subquery

IF( someField IS NULL,(SELECT A FROM A WHERE userID=1),(SELECT BFROM BWHERE userID=1)) AS userAge

So as you can see IF is pretty versatile and it does get quite a run in my use of MySQL.

CASE

CASE statements are IF statements on steroids. So I use this when I want to do massive data conversions or do things like age ranges. I tend to favour the CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END format rather than the alternative. It can be more verbose but most of the time I end up having different conditions on every statement. Plus I can nest multiple IFs in one line without the need of brackets that are required in an IF statement.

Age ranges using CASE

CASE

WHEN age BETWEEN 0 AND 1 THEN ‘newborn’

WHEN age BETWEEN 2 AND 3 THEN ‘toddler’

WHEN age BETWEEN 4 AND 5 THEN ‘preschooler’

WHEN age BETWEEN 6 AND 12 THEN ‘primary schooler’

WHEN age BETWEEN 13 AND 18 THEN ‘secondary schooler’

WHEN age BETWEEN 19 AND 21 THEN ‘university student’

WHEN age > 18 THEN ‘adult’

END AS ‘educationBracket’

Multiple Conditions in a CASE

CASE

WHEN age BETWEEN 0 AND 1 AND gender = ‘Male’ THEN ‘newborn boy’

WHEN age BETWEEN 0 AND 1 AND gender = ‘Female’ THEN ‘newborn girl’

END AS ‘ageGender’

Using a CASE statement with an aggregate function

SUM( CASE WHEN age = 1 THEN 10 WHEN age = 2 THEN 12 WHEN age > 3 THEN 14 END) AS totalMinsSupervisionPerDay