MySQL Operators are your friend in fact. In MySQL, just as in all programming languages, we have these nifty little things called operators. Operators are pretty darn important, as they allow us to perform meaningful operations on our data. Some of the MySQL operators include LIKE, LIKE%…%, NOT LIKE, =, !=, REGEXP, REGEXP^…$, NOT REGEXP, =”, !=”, IN(…), NOT IN(…), BETWEEN, NOT BETWEEN, IS NULL, and IS NOT NULL. Wow, that’s a lot of operators. Yep, it sure is. You might be wondering, so how the heck am I going to use all of these operators and what are they good for? Those are great thoughts, and we’ll jump right into some of the more common use cases now.
>= and <=
Using the greater than or equal to along with the less than or equal to operators is pretty cool. Let’s remember our friends table that we’ve been working with throughout this MySQL series. What if we wanted to check to see who was born during the 1980’s, how could we do that? Let’s check out a query of how to accomplish this:
mysql> select first_name, last_name, year(birthday) from friends
-> where birthday >= '1980-01-01' and birthday <= '1989-12-31';
+------------+-----------+----------------+
| first_name | last_name | year(birthday) |
+------------+-----------+----------------+
| Jim | Jones | 1985 |
+------------+-----------+----------------+
1 row in set (0.00 sec)
Pretty Slick! By including these operators in our select statement, we found that Jim Jones was the only person from our friends that was born in the Awesome 80’s. Be honest, you love Duran Duran.
Between
It turns out using a combination of greater than or equal to and less than or equal to is a pretty common scenario. MySQL gives you a dedicated operator to do this same thing with much less typing. It works just like this:
mysql> select first_name, last_name, year(birthday) from friends
-> where birthday between '1980-01-01' and '1989-12-31';
+------------+-----------+----------------+
| first_name | last_name | year(birthday) |
+------------+-----------+----------------+
| Jim | Jones | 1985 |
+------------+-----------+----------------+
1 row in set (0.03 sec)
Voila! You see, it works just the same – Jim Jones is our only Hair Band Hero.
Not Between
Now check it out. I know you like that between operator. Today is your lucky day partner, because not only does MySQL give you a between operator, they also provide you with a not between operator. Use this to filter out anything not in a range. So we know anyone else that wasn’t born in the 80’s is not so cool. So let’s check for the non cool friends now:
mysql> select first_name, last_name, year(birthday) from friends
-> where birthday not between '1980-01-01' and '1989-12-31';
+------------+------------+----------------+
| first_name | last_name | year(birthday) |
+------------+------------+----------------+
| Brenda | Penblanc | 1970 |
| Dave | Chappelle | 1973 |
| Emmet | Brickowski | 2013 |
| Frankie | Farmington | 1977 |
| Jack | Penblanc | 1967 |
| Jacob | Olivio | 1972 |
| Mary | Olivio | 1972 |
| Sheila | Farmington | 1977 |
| Sofia | Vergara | 1972 |
+------------+------------+----------------+
9 rows in set (0.00 sec)
Jumpin Jack Flash! Did you see that?! Our not between operator just gave us a list of friends not born in the 80’s with ease, style, and grace. So cool.
OR
This is a great operator and you may be used to using it in your other programming efforts. When using JavaScript or PHP, we often use the or operator in conditional branches. Do this action if this is true, or that is true. We can use it in MySQL as well and it is quite handy. Let’s look for anyone born in 1972 or 1973. Check it:
mysql> select first_name, last_name, year(birthday) from friends
-> where year(birthday)='1972' or year(birthday)='1973';
+------------+-----------+----------------+
| first_name | last_name | year(birthday) |
+------------+-----------+----------------+
| Dave | Chappelle | 1973 |
| Jacob | Olivio | 1972 |
| Mary | Olivio | 1972 |
| Sofia | Vergara | 1972 |
+------------+-----------+----------------+
4 rows in set (0.00 sec)
So we can see that using by using or, we were able to grab results for two scenarios. We can add many together as well. Let’s look for people born in the year 1967, 1970, and 1977.
mysql> select first_name, last_name, year(birthday) from friends
-> where year(birthday)='1967' or year(birthday)='1970' or year(birthday)='1977';
+------------+------------+----------------+
| first_name | last_name | year(birthday) |
+------------+------------+----------------+
| Brenda | Penblanc | 1970 |
| Frankie | Farmington | 1977 |
| Jack | Penblanc | 1967 |
| Sheila | Farmington | 1977 |
+------------+------------+----------------+
4 rows in set (0.00 sec)
IN
This operator is also quite useful. Instead of chaining so many or operators together, you could use the in operator to fetch similar results. Here is the syntax for this approach:
mysql> select first_name, last_name, year(birthday) from friends
-> where first_name in ('Brenda','Frankie','Jack','Sheila');
+------------+------------+----------------+
| first_name | last_name | year(birthday) |
+------------+------------+----------------+
| Brenda | Penblanc | 1970 |
| Frankie | Farmington | 1977 |
| Jack | Penblanc | 1967 |
| Sheila | Farmington | 1977 |
+------------+------------+----------------+
4 rows in set (0.09 sec)
Just like any other programming language or approach, there are many ways to skin the cat so to speak. We’re simply looking at some common approaches, then you get to choose which works best for you.
Like and Not Like
The like and not like operators are very useful, especially if used with wildcard characters. So first up, we can try to find everyone in our last name column where they name is like Penblanc.
mysql> select last_name from friends where last_name like 'Penblan'; Empty set (0.00 sec) mysql> select last_name from friends where last_name like 'Penblan_'; +-----------+ | last_name | +-----------+ | Penblanc | | Penblanc | +-----------+ 2 rows in set (0.00 sec)
Attention Grasshopper! Did you notice that we just did two queries right there? The first one returned an empty set, why is that? You would think that Penblan is like Penblanc. We were looking for all the Penblancs in our table, searching for where something is like Penblan seems reasonable enough, no? Well, it turns out with like, you either have to have an exact match or use a wild card. The second query uses a wildcard in the last character position of the Penblanc last name. You can see that it successfully returns the two entries for Penblanc in the column when we search this way.
Another scenario with the like operator is in conjunction with the multi-character wildcard, which in MySQL is the percent % symbol. Imagine we had no clue how to spell Emmet’s last name of Brickowski. If we at least know that the first part of his name starts with Brick, then we can combine what we know with a multi-character wildcard symbol and get the data we need. Let’s try it out:
mysql> select first_name, last_name, year(birthday) from friends
-> where last_name like 'Brick%';
+------------+------------+----------------+
| first_name | last_name | year(birthday) |
+------------+------------+----------------+
| Emmet | Brickowski | 2013 |
+------------+------------+----------------+
1 row in set (0.00 sec)
I see an Emmet right there, do you?! Man, that Lego Movie is awesome. If you haven’t seen it, you absolutely must! If you get nothing else out of this tutorial – make sure to watch Emmet save the world in the Lego Movie. You’ll have a better day because of it.
The other side of the coin with like is not like. In other words, we can do negation. Let’s practice. Show me everyone who was not born in 1972.
mysql> select * from friends;
+------------+------------+------------+------------+----------+
| first_name | last_name | cell_phone | birthday | house_id |
+------------+------------+------------+------------+----------+
| Brenda | Penblanc | 719-4521 | 1970-02-15 | 5 |
| Dave | Chappelle | 977-5555 | 1973-08-24 | 4 |
| Emmet | Brickowski | 987-1244 | 2013-07-15 | 3 |
| Frankie | Farmington | 978-1235 | 1977-04-17 | 6 |
| Jack | Penblanc | 581-1841 | 1967-04-23 | 5 |
| Jacob | Olivio | 512-3411 | 1972-10-01 | 7 |
| Jim | Jones | 423-1423 | 1985-07-24 | 1 |
| Mary | Olivio | 412-9823 | 1972-11-11 | 7 |
| Sheila | Farmington | 591-8522 | 1977-07-12 | 6 |
| Sofia | Vergara | 487-4612 | 1972-07-10 | 2 |
+------------+------------+------------+------------+----------+
10 rows in set (0.00 sec)
mysql> select first_name, last_name, year(birthday) from friends
-> where year(birthday) not like '1972';
+------------+------------+----------------+
| first_name | last_name | year(birthday) |
+------------+------------+----------------+
| Brenda | Penblanc | 1970 |
| Dave | Chappelle | 1973 |
| Emmet | Brickowski | 2013 |
| Frankie | Farmington | 1977 |
| Jack | Penblanc | 1967 |
| Jim | Jones | 1985 |
| Sheila | Farmington | 1977 |
+------------+------------+----------------+
7 rows in set (0.00 sec)
That’s pretty slick. In the first query, we return all friends in our friends table and we can see that Jacob, Mary, and Sofia were all born in 1972. With the second query we use the not like operator to perform negation and show all people who were not born in 1972. Very cool!
A few more examples that include use of wildcard characters is in order. Let’s say that we want to see all friends that have the letter e within their first name. Let’s try it out:
mysql> select * from friends where first_name like '%e%'; +------------+------------+------------+------------+----------+ | first_name | last_name | cell_phone | birthday | house_id | +------------+------------+------------+------------+----------+ | Brenda | Penblanc | 719-4521 | 1970-02-15 | 5 | | Dave | Chappelle | 977-5555 | 1973-08-24 | 4 | | Emmet | Brickowski | 987-1244 | 2013-07-15 | 3 | | Frankie | Farmington | 978-1235 | 1977-04-17 | 6 | | Sheila | Farmington | 591-8522 | 1977-07-12 | 6 | +------------+------------+------------+------------+----------+ 5 rows in set (0.00 sec)
The query above says to use as many wildcard characters as you need until you hit one letter e, then include all remaining characters to the right of that one. If you’re familiar with regular expressions, this style of searching might ring a bell with you.
How about friends that have a last name which begins with the letter V?
mysql> select * from friends where left(last_name,1)='V'; +------------+-----------+------------+------------+----------+ | first_name | last_name | cell_phone | birthday | house_id | +------------+-----------+------------+------------+----------+ | Sofia | Vergara | 487-4612 | 1972-07-10 | 2 | +------------+-----------+------------+------------+----------+ 1 row in set (0.00 sec)
Ah yes, there she is, our Sweet Sofia.
What about friends that have the letter o in their last name? Let’s try it out:
mysql> select * from friends where last_name like '%o%'; +------------+------------+------------+------------+----------+ | first_name | last_name | cell_phone | birthday | house_id | +------------+------------+------------+------------+----------+ | Emmet | Brickowski | 987-1244 | 2013-07-15 | 3 | | Frankie | Farmington | 978-1235 | 1977-04-17 | 6 | | Jacob | Olivio | 512-3411 | 1972-10-01 | 7 | | Jim | Jones | 423-1423 | 1985-07-24 | 1 | | Mary | Olivio | 412-9823 | 1972-11-11 | 7 | | Sheila | Farmington | 591-8522 | 1977-07-12 | 6 | +------------+------------+------------+------------+----------+ 6 rows in set (0.00 sec)
It looks like Emmet, Frankie, Jacob, Jim, Mary, and Sheila all have last names that contain the letter o.
Now You Know What MySQL Operators Are!
In this episode we covered a lot of ground in dealing with MySQL operators and how to apply them to your queries to get the data you want. These examples just scratch the surface of what you can do with operators. In some ways, the examples may seem trivial, but the main takeaway is that you can see how we are able to think about what we want (declarative nature), and construct the MySQL syntax to make that happen.
