Understanding MySQL’s IF() function – with examples.

Conditional logic is integral for just about any application. Most programming languages support some form of if/else construct. MySQL is a dialect of SQL, which is a declarative language. But, it also includes support for conditional flow as do other languages such as Python, PHP, and Java. In this blog post, I will look at the IF() Function for similar if/else type of functionality.

pair-of-feet-in-front-of-two-arrows
Photo by Jon Tyson on Unsplash

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:


  • Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
  • MySQL 8.0.13

I’ll use this table and data:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT * FROM names;
+------+------------+
| id   | first_name |
+------+------------+
|    4 | Harry      |
|  333 | Tomas      |
|    8 | Shelly     |
|   10 | Ricky      |
|   99 | Abby       |
|   99 | Abby       |
|  100 | Bill       |
+------+------------+
7 rows in set (0.00 sec)

I want to stress that I will not necessarily demonstrate or cover IF/ELSE conditional flow in this blog post, but instead look into the IF() function itself; exploring its mannerisms and quirks (there are a couple to know of).

Let’s set a user-defined session variable with the id value for the name ‘Shelly’ to use in this example: (Have a look at my blog post, MySQL Transactions and User-defined session variables, where I cover them in greater depth.)

1
2
3
4
5
mysql> SET @Shelly_id := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id INTO @Shelly_id FROM names WHERE first_name = 'Shelly';
Query OK, 1 row affected (0.10 sec)

When calling the IF() function, you provide it 3 arguments (or expressions). Let’s see it in action then go over how it works.

Below I specify the @Shelly_id as the first argument, supplying strings as arguments 2 and 3. Dependent upon how the first argument evaluates, one of those 2 remaining arguments will return.

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id = 4, 'That is Shelly''s', 'Does not belong to Shelly') AS returned_message;
+---------------------------+
| returned_message          |
+---------------------------+
| Does not belong to Shelly |
+---------------------------+
1 row in set (0.00 sec)

In this first example, since the @Shelly_id variable is not equal to 4, that expression is considered FALSE and the 3rd expression is returned.

But, when we compare the @Shelly_id variable to 8, that expression is TRUE, which returns the 2nd expression as seen below:

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id = 8, 'That is Shelly''s', 'Does not belong to Shelly') AS returned_message;
+------------------+
| returned_message |
+------------------+
| That is Shelly's |
+------------------+
1 row in set (0.00 sec)

Pretty simple concept right? But, there are a couple of hidden nuances to be aware of.

I will UPDATE the id for ‘Shelly’ below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> UPDATE names SET id = 0
    -> WHERE first_name = 'Shelly';
Query OK, 1 row affected (0.22 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT *
    -> FROM names
    -> WHERE first_name = 'Shelly';
+------+------------+
| id   | first_name |
+------+------------+
|    0 | Shelly     |
+------+------------+
1 row in set (0.00 sec)

Let’s re-establish the @Shelly_id variable and assign it the updated column value with SELECT INTO syntax (Visit my blog post, MySQL SELECT INTO Syntax Part 1 – Uses With Variables, for a deeper dive on this type of variable assignment.):

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SET @Shelly_id := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id INTO @Shelly_id FROM names WHERE first_name = 'Shelly';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @Shelly_id;
+------------+
| @Shelly_id |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

Now pay attention to the following queries.

The 1st expression conditional is TRUE, therefore returning the 2nd expression. Nothing much new here.

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id = 0, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-----------------------------+
| returned_message            |
+-----------------------------+
| That is Shelly's new id now |
+-----------------------------+
1 row in set (0.00 sec)

In the next statement below, the 1st expression is the value of the @Shelly_id variable, which is 0. However, there is no comparison here. And a value of 0 will always return the 3rd expression.

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-------------------------+
| returned_message        |
+-------------------------+
| That is not Shelly's id |
+-------------------------+
1 row in set (0.00 sec)

Next, since the @Shelly_id variable is not equal to 8 (remember it’s 0), the 1st expression here (see below) is deemed TRUE returning the 2nd argument of the 3.

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id <> 8, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-----------------------------+
| returned_message            |
+-----------------------------+
| That is Shelly's new id now |
+-----------------------------+
1 row in set (0.00 sec)

On the converse, the 1st expression here evaluates to FALSE because the @Shelly_id variable is at this time, equal to zero which returns the 3rd expression.

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id <> 0, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-------------------------+
| returned_message        |
+-------------------------+
| That is not Shelly's id |
+-------------------------+
1 row in set (0.00 sec)

Let’s set the @Shelly_id variable to NULL:

1
2
3
4
5
6
7
8
9
10
mysql> SET @Shelly_id := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@Shelly_id IS NULL, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-----------------------------+
| returned_message            |
+-----------------------------+
| That is Shelly's new id now |
+-----------------------------+
1 row in set (0.00 sec)
The 1st expression evaluates to TRUE returning the 2nd since the @Shelly_id variable is NULL (as of now).

In the below query, the 3rd expression is returned because the 1st one evaluates to FALSE:

1
2
3
4
5
6
7
mysql> SELECT IF(@Shelly_id IS NOT NULL, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-------------------------+
| returned_message        |
+-------------------------+
| That is not Shelly's id |
+-------------------------+
1 row in set (0.00 sec)

But also note, that if the 1st expression is NULL, then the 3rd expression is returned:

1
2
3
4
5
6
7
mysql> SELECT IF(NULL, 'That is Shelly''s new id now', 'That is not Shelly''s id') AS returned_message;
+-------------------------+
| returned_message        |
+-------------------------+
| That is not Shelly's id |
+-------------------------+
1 row in set (0.00 sec)

This is the second important distinction to digest. If expression 1 is either 0 or NULL, the 3rd expression is always returned.

Additional reading: MySQL IF Function

Although I have not provided an example of conditional flow with the IF() function, my hope is you will take what you have read here, explore it, and improve on it for your particular use case. I’d love to hear about it in the comments below.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 8.0 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

One thought on “Understanding MySQL’s IF() function – with examples.”

  1. Pingback: Conditional logic with SUM() and the IF() functions in MySQL… | Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.