Logical Functions


IF Expressions

Note that some SQL platforms use IIF instead of IF, as MySQL does.

When nesting IF statements, as shown below, it is necessary to use parentheses very deliberately.

IF([conditions], [value if conditions met], [value if conditions note met])

SELECT IF(u_users.country = 'US', 'US-Based',
          IF(u_users.country = 'N/A', 'Not Applicable', 'Foreign')) AS location,
       COUNT(u_users.user_guid)
  FROM (SELECT DISTINCT user_guid,
               country
          FROM users
         WHERE user_guid IS NOT NULL
           AND country IS NOT NULL) AS u_users
 GROUP BY location;
location COUNT(u_users.user_guid)
Foreign 1263
Not Applicable 5642
US-Based 9356

CASE Expressions

CASE returns a singular value based on conditional test(s). There are two syntaxes that can be used with CASE.

CASE
    WHEN [condition set 1] THEN [result when condition 1 met]
    WHEN [condition set 2] THEN [result when condition 2 met]
    ...
    ELSE [result when no conditions met]
 END AS alias

SELECT CASE
            WHEN u_users.country = 'US' THEN 'US-Based'
            WHEN u_users.country = 'N/A' THEN 'Not Applicable'
            ELSE 'Foreign'
        END AS location,
        COUNT(u_users.user_guid)
  FROM (SELECT DISTINCT user_guid,
               country
          FROM users
         WHERE user_guid IS NOT NULL
           AND country IS NOT NULL) AS u_users
 GROUP BY location;

The simpler syntax when no values need to be manipulated:

CASE column_name or expression
    WHEN [value 1] THEN [result when row=value 1]
    WHEN [value 2] THEN [result when row=value 2]
    ...
    ELSE [result when row does not equal specified values]
 END AS alias

SELECT CASE u_users.country
            WHEN 'US' THEN 'US-Based'
            WHEN 'N/A' THEN 'Not Applicable'
            ELSE 'Foreign'
       END AS location,
       COUNT(u_users.user_guid)
  FROM (SELECT DISTINCT user_guid,
               country
          FROM users
         WHERE user_guid IS NOT NULL
           AND country IS NOT NULL) AS u_users
 GROUP BY location;

Order of Operations in Logical Expressions

The evaluation order of logical expressions in SQL is:

  1. NOT
  2. AND
  3. OR
CASE WHEN "condition 1" OR "condition 2" AND "condition 3"

This statement evaluates as (2 AND 3) OR 1.

CASE WHEN "condition 3" AND "condition 1" OR "condition 2"

This statement evaluates as (3 AND 1) OR 2.

CASE WHEN ("condition 1" OR "condition 2") AND "condition 3"

This statement evaluates as (1 OR 2) AND 3.

Some content in this note are taken from the Jupyter Notebook below. They are accessible as part of the “Managing Big Data with MySQL” course on coursera.org, and licensed by Jana Schaich Borg under CC BY-NC 4.0.

  • MySQL Exercise 10 Useful Logical Operators

Other content for this note is taken from the Coursera course “Managing Big Data with MySQL.”