Chapter 4. Better Where Clauses Filtering Rows
Figure 4-1.
Here’s your opportunity to dig in. You’ve already tried out WHERE for simple table row filters, but now you can perform an in-depth exploration of WHERE to see what it can really do.
In this chapter, you’ll use AND to find rows that meet two or more requirements. You’ll switch to OR if you want rows that satisfy any of several conditions. And you will even unearth new keyword artifacts to aid in your quest for missing items, ranges of values, or text patterns.
So what are you waiting for? Turn the page and let the excavation begin. WHERE is your new best friend.
Misplaced identification
Uh oh, Abby doesn’t look happy. Let’s check back in with her to see what’s going on.
Figure 4-2.
We can use a SELECT query with the filter: WHERE country = 'USA'
to find Abby some locations within the USA so she won’t need her passport to travel. Here’s the first few results:
Figure 4-3.
Multiple row requirements
We were able to find locations within the United States, but Abby and her friend Katie wouldn’t need to fly to all of the places we found.
What if we try a SELECT query but use a filter to require plane travel?
The first few rows of those results give us:
Figure 4-4.
Figure 4-5.
Combining your queries
We can combine the two things we’re looking for, USA cities that require plane travel, into a single query with the keyword AND. The resulting rows must satisfy both conditions.
Figure 4-7.
This query effectively asks SQL for one group of all the places in Abby’s table within the US. And it also asks for a group of all the places that require plane travel. Then the query returns only the cities that belong to both groups.
Figure 4-8.
Use AND to satisfy two conditions
When your table rows need to meet two (or more) requirements, use the keyword AND to join the conditional statements in your WHERE filter. Ultimately, rows that show up in the results will satisfy all the conditions.
Searching for cities in the US that Abby can fly to looks like this:
Figure 4-9.
And here’s the SELECT query Abby needs, applied to her locations table:
Figure 4-10.
Figure 4-11.
Excellent question. They don’t match.
When we include:
WHERE country = 'USA' AND travel_mode = 'plane'
only rows with country exactly equal to “USA” and travel_mode exactly equal to “plane” show up in the results. If a row is missing a value for either the country column or the travel_mode column, it’s ruled out and doesn’t show up in the output. Speaking of excellent questions, here are a few more...
Figure 4-17.
Absolutely. You need OR.
You’ve been combining queries in your WHERE clauses with the keyword AND. This means you only want rows that satisfy both requirements. If instead you’d like to find rows that satisfy one requirement or the other, you can join your conditional statements up with the SQL keyword OR.
To be OR not to be
Figure 4-18.
Here’s how Abby can search her locations table for places in Canada OR Mexico:
Figure 4-19.
When you use OR, rows only need to satisfy one your conditional statements, so submitting this query to SQL returns all cities in either country of interest:
Figure 4-20.
Code Magnets
Figure 4-21.
Oh no! We had carefully laid out the SQL code to query the locations table for the names of cities that are either less than four hours away or can be reached by car, but all the magnets fell on the floor. It’s your job to put them back together again to recreate the command. Be careful; a few extra magnets that you won’t need got mixed in. Add as many commas and semicolons as necessary.
Figure 4-22.
Code Magnets Solution
Figure 4-23.
Oh no! We had carefully laid out the SQL code to query the locations table for the names of cities that are either less than four hours away or can be reached by car, but all the magnets fell on the floor. It was your job to put them back together again to recreate the command.
Figure 4-24.
Fireside Chats
Tonight’s talk: AND and OR compare capabilities
Figure 4-25.
| AND | OR |
| I can’t believe I’ve even been asked to do this Fireside Chat. There’s just no comparison between us! I’m clearly the superior way to link up conditional statements. I make sure your results meet ALL the necessary requirements. | |
| That is, unless what you really need is to find rows that satisfy ANY of the requirements. | |
| Sheesh, that’s silly. Why would someone ever need to do that? | |
| Well, you dolt, to look for items belonging to one of several categories for instance. Like cities in Canada, USA, or Mexico... or boost ingredients that help with “Protein” or “Energy”. You’re utterly useless in those situations. | |
| I mean, I guess. And I wouldn’t even want to be a part of those special cases. But what about when someone has really specific items in mind, such as places that require plane travel, are located in the US, AND have “warm to cold” weather? | |
| Yeah, okay, okay. Narrowing things down is your lane. But I’m more useful for broad requirements. Not just text categories but also for numbers. For example, boost options that are low in calories or low in price. | |
| You work across multiple columns? | |
| Of course! I’m not some one-trick pony. I suppose we do have that in common. | |
| Yeah, maybe there was a point to this chat after all. We are alike. But just different enough to offer diverse benefits. | |
Gee... WHERE sappy_score >= 100 AND gone_soft = 'yes'; |
The difference between AND and OR
Still need convincing? Below you’ll find a snippet of the boosts table along with queries to demonstrate all possible combinations of conditions that evaluate to true or false with AND and OR between them.
selected_boosts |
|||||
| name | category | price |
calories |
fat_g |
benefits |
| chia seeds | Healthy Fats | 0.30 | 135 | 9 | Rich in omega-3 fatty acids |
| protein power | Protein | 1.00 | 120 | 2 | Concentrated source of protein |
| turmeric | Spices | 0.25 | 30 | 1 | Anti-inflammatory, antioxidants |
| honey | NULL | 0.20 | 65 | 0 | Natural sweetener, antioxidants |
Figure 4-26.
BE the Conditional
Figure 4-27.
Your job is to play the conditional and determine whether or not you achieve inner peace and a result from the selected_boosts table. For extra karma, note down why two of the results are a bit different than the rest.
SELECT category FROM selected_boosts |
Do you get a result? If so, what? |
WHERE name = 'chia seeds' AND fat_g <> 2; |
................. |
WHERE name = 'coconut water' AND fat_g >= 2; |
................. |
WHERE name = 'chia seeds' AND fat_g = 0; |
................. |
WHERE name = 'chia seeds' OR fat_g < 2; |
................. |
WHERE name = 'chia seeds' OR fat_g = 5; |
................. |
WHERE name = 'coconut water' OR fat_g = 0; |
................. |
Be the Conditional Solution
Figure 4-28.
Your job was to play the conditional and determine whether or not you achieve inner peace and a result from the selected_boosts table. For extra karma, note down why two of the results are a bit different than the rest.
Note
See the bottom of this page for more information.
Figure 4-29.
Those NULL values may cause you problems in future queries if you need to sort the rows or find specific information. And as it turns out, you can’t directly select a NULL using the equal symbol.
Use IS NULL to find NULLs
Figure 4-30.
boosts_info |
|
| name | category |
| chia seeds | Healthy Fats |
| protein power | Protein |
| turmeric | Spices |
| honey | NULL |
Note
There’s our NULL.
You can’t select a NULL value directly.
Remember that NULL isn’t equal to anything, not even NULL itself. NULL represents missing data, which is entirely unknown. So none of the following queries work:
Figure 4-31.
But you can select it using keywords.
NULL isn’t equal to anything, but the special SQL keywords, IS NULL, can help you find your missing values.
Figure 4-32.
IS NULL in your console
Figure 4-33.
Let’s help Abby out by looking for US cities with NULL in the travel_mode column:
Figure 4-34.
Selecting ranges using AND and comparison operators
Back at the Head First Cafe, the staff now wants to find boost ingredients in a specific price range. They need the names of boosts that cost at least $0.20 but no more than $0.50.
boosts |
|||||
| name | category | price |
calories |
fat_g |
benefits |
| chia seeds | Healthy Fats | 0.30 | 135 | 9 | Rich in omega-3 fatty acids |
| flaxseeds | Healthy Fats | NULL | 120 | 12 | Rich in omega-3 fatty acids |
| matcha | Antioxidant | 0.50 | 3 | 1 | Antioxidants, mild caffeine boost |
| ginger | Spices | 0.10 | 5 | 1 | Anti-inflammatory |
| protein power | Protein | 1.00 | 120 | 2 | Concentrated source of protein |
| turmeric | Spices | 0.25 | 30 | 1 | Anti-inflammatory, antioxidants |
| honey | NULL | 0.20 | 65 | 0 | Natural sweetener |
| cinnamon | Spices | 0.05 | 5 | 0 | Anti-inflammatory, antioxidants |
Here’s a query with AND and comparison operators to find items matching that price range:
Figure 4-35.
Just BETWEEN us... there’s a better way
While the previous query with AND and comparison operators did work, we can use the BETWEEN keyword instead to save some typing. Notice that BETWEEN also includes the endpoints ($0.20 and $0.50). BETWEEN is equivalent to using the <= and >= symbols, not the < and > symbols.
Figure 4-36.
This will give you the exact same results as the query on the previous page. But note how much quicker it is to type.
Figure 4-37.
Meanwhile, Abby dreams of sunshine...
It’s almost wintertime in New York, and Abby has been dreaming of a vacation to a tropical locale.
Figure 4-44.
Figure 4-45.
Save time with LIKE
There are simply too many variations for the weather descriptions, making Abby’s query quite long and difficult to type out. Using all those ORs is going to take a while. Luckily, there’s a time-saving keyword–LIKE–that, used with a wildcard, looks for part of a text string and returns any matches.
If Abby wants to find any city with “warm” as a part of its text in the weather column, she can write this query with LIKE:
Figure 4-46.
When Abby tries the query in her SQL console, she finds several options that all have “warm” in their weather descriptions.
Figure 4-47.
I’d LIKE to buy a wildcard, please
LIKE likes to team up with two wildcard characters: the percent sign, %, and the underscore, _. Wildcards are stand-ins for the characters that are actually there. And they can represent any character in a string.
Just as you saw in the example, the percent sign, %, can stand in for any number of unknown characters.
Figure 4-48.
The underscore, _, stands for just one unknown character.
Figure 4-49.
Figure 4-50.
You can use as many wildcards you need.
In the “warm” example, we looked for strings that contained the word “warm”, and “warm” could come at the beginning of the string, at the end, or by itself. So we had % both at the beginning and the end since characters could go in either spot. In the “U” example, we only wanted countries that start with “U”. Characters could only go after “U”, so that’s where we placed the %.
You can build even more complex patterns to use with LIKE. For example,
Figure 4-51.
matches “Canada”, “Japan”, and “Tanzania”.
You’re either IN...
Figure 4-56.
Figure 4-57.
Instead of all those ORs, we can simplify the query with the keyword IN. Use IN with a set of values enclosed by parentheses. When the column value matches one of the items in the set, the results include that row.
Figure 4-58.
Figure 4-59.
... or you are NOT IN
Figure 4-60.
Of course, now Abby’s curious. Which places are in countries other than the ones her brother is considering?
To find cities in other countries, we can add the keyword NOT to our IN statement. NOT gives you the opposite results, anything that doesn’t match the set.
Figure 4-61.
Figure 4-62.
And that’s NOT all
You can use NOT with BETWEEN and LIKE, too. In these cases, NOT goes right after WHERE. Here are some examples:
Figure 4-64.
Figure 4-65.
WHERE for the win
Figure 4-70.
Sounds like Abby’s in much better spirits at the end of this chapter than when we found her in the beginning. You’re making great progress. In fact, you’re getting quite the reputation for your SQL know-how. Here’s Josh with a new request:
Figure 4-71.
SQLcross
Figure 4-72.
Filter out all the noise and fill in this crossword puzzle about filtering SQL rows.
Figure 4-73.
Across
1. This keyword is equivalent to using two comparison statements, one with >= and one with <=.
4. To avoid missing value in a particular column, try the keywords IS ______. (2 words)
7. This wildcard stands in for exactly one character.
8. The minimum number of unknown characters that % could represent when used with LIKE.
9. Rows match ______ condition(s) joined with OR; rows match ______ condition(s) joined with AND. (2 words)
Down
2. Use these to look for patterns with LIKE.
3. To simplify WHERE NOT col = 'example', switch to this symbol instead. (2 words)
4. The keywords ______ make sure your column values are not part of the provided set. (2 words)
5. To get rows that either cost less than $0.50 or belong to the “Antioxidant” category, try this in your WHERE.
6. This keyword is well suited to find all names beginning with the letter “J”.
Answers in “SQLcross Solution”
Your SQL toolbox
Figure 4-74.
And another one! As you cruised through Chapter 4, you collected some valuable skills for your SQL toolbox. Check out these new keywords and give yourself some props for stepping up to the challenge.
Figure 4-75.
SQLcross Solution
Figure 4-76.
Filter out all the noise and fill in this crossword puzzle about filtering SQL rows.
From “Your SQL toolbox”
Figure 4-77.
Across
1. This keyword is equivalent to using two comparison statements, one with >= and one with <=.
4. To avoid missing value in a particular column, try the keywords IS ______. (2 words)
7. This wildcard stands in for exactly one character.
8. The minimum number of unknown characters that % could represent when used with LIKE.
9. Rows match ______ condition(s) joined with OR; rows match ______ condition(s) joined with AND. (2 words)
Down
2. Use these to look for patterns with LIKE.
3. To simplify WHERE NOT col = 'example', switch to this symbol instead. (2 words)
4. The keywords ______ make sure your column values are not part of the provided set. (2 words)
5. To get rows that either cost less than $0.50 or belong to the “Antioxidant” category, try this in your WHERE.
6. This keyword is well suited to find all names beginning with the letter “J”.
Get Head First SQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
