Join Bill Weinman for an in-depth discussion in this video Filtering data with WHERE, LIKE, and IN, part of SQL Essential Training.
Most of the time when you're getting data from a table. You're looking for specific data. Data that fits a specific criteria. The where clause gives you just the tools you need. To get just the rows you're looking for instead of the entire table. For this lesson we're going to use the world dot db database. And we're going to use the country table, which looks like this. And you can see it's a pretty big table. It has a lot of rows and columns. It says here we got 239 rows from that query. Now if you just want to know which countries have a population.
Under 100,000, it's easy to construct a query like this. I would say, select, and I'll just pick a few of the columns here. Say, name, continent, and population. And I'm going to add a where clause before the semicolon here, where population is less than 100000. And I'm going to give it a sort order over here. Order by population descending.
I'll show you more about the order clause later. And we'll go ahead and run this query. And you see there's the results of our query, now we have just 40 rows, and their rows where the population is under a hundred thousand you see that its a descending list starting with the larger numbers at the top. So the where clause takes a Boolean expression and rows are returned only if the Boolean expression evaluates as true for that row. So here's the Boolean expression, Population less than 100,000.
And these are just the rows from the table where that expression evaluates to true, where that condition is met. Now you'll notice that there's some countries missing from this list where the population is unknown. Where the population is very small like Antarctica for example because those countries have a null in their population column. We can add an expression to the where clause to include those row's and I'm going to do that like this I'm going to say and population is null and actually I want an or and not an and.
And so now I have two expressions. I have population is less than a 100,000. and population is null. These are two separate expressions. And they're bound by the Boolean operator or. So this entire expression that includes both of these Boolean expressions. Is true if either of these different conditions evaluates to truce. So, when I press go I'm now going to get 47 rows instead of 40. And you'll notice that down here at the bottom, because these nulls sort as smaller than zero.
So, down here at the bottom we have these rows where the value is unknown, or very small. And so its null in the table so that includes Antarctica this British Indian ocean territory and Oceana and that's those 7 rows with the null value in them. So the or keyword is a Boolean operator boolean operators are used to bind different expressions together you can use parenthesis as well to give priority to expressions.
Here I just use an or operator to bind the two Boolean expressions and I get the results where either of the two expressions are true. I could use and to bind expressions so that rows are only returned when both of the expressions are true. For example if I just want the rows where the continent is Oceana. I can say and. Equals Oceania - I'm spelling it correctly with that i in there that I don't typically pronounce. I'm probably pronouncing it wrong.
And I press go. And now I just get the rows that get Oceania in the continent and the population is under 100,000. So now both of these expressions, population is less than a 100 thousand and continent equals Oceania, both of these expressions have to be true because they're bound together with the Boolean operator "and". There are also two notable operators that are unique to SQL for filtering results. Let's start with the like operator.
So I'm just going to take everything up to the semi-colon here And i'm going to say where name. And I'm going to use the like operator. And I'm going to put a special string inside the single quotes here. Percent mark the word island. And another percent mark. And I'm going to say order by name. So this will return rows for all the countries whose name includes the word Island. Press Go and here's all these. It says there's 18 of them.
And so these all have the word Island in their name some place. So first of all, notice the percent signs. The percent sign is a wildcard in SQL. It matches zero or more characters in a string. So by putting the percent signs before and after the string, I've matched all the strings with island anywhere in the string. I could only match those strings that end with the word island by omitting the percent sign at the end of the string. And so if I take out this percent sign, Now, matching strings have to actually end with the word island because there's no wild card after it.
So, it's not going to match anything after those characters. So, if I press go now, you'll notice we only get three rows. Because these are the ones that don't have an s at the end of the word island, so it's not Islands, It's just island by itself without the s. Or, I could only match those strings that begin with island by emitting the percent sign at the beginning and adding it at the end, and I press go. And, you'll notice I get no rows returned because there aren't any rows in the table.
That have a name column that starts with the word island. You can also match any single character by using the underscore like this. So this will match any string that the second letter is an a. So it matches anything for the first letter, only an a for the second letter, and then anything else in the rest of the string by using the percent sign. So when I say go, notice we get all these rows. It says there's 57 of them. And they all have an A in the second character of the string.
So the underscore matches the single character and the percent sign matches zero or more characters. There's one more operator you should know about for filtering results. And this is the in operator and this one works a little bit differently. So here I'm going to say where name in and then I'm going to put in parenthesis a list. Actually I don't want to match name; I want to match continent. And I'm going to be match countries where their continent is either Europe or Asia. I'm going to say Europe. And comma Asia. And I got a couple of extra spaces in there.
And I'll press go and now you see we have just the countries where the continent is Asia or Europe. So the in operators use to select results that match values in a list. And this query returns those matching rows where the continent column matches this list or Europe or Asia. The In Operators also used for subselects, and I'll cover subselects in a later chapter in this course. So the where clause allows you to filter your results to get just the rows you need and SQL provides the powerful like and in operators to allow you to refine your searches beyond the normal Boolean Operators.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.