Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the time when you're getting data from a table, you're looking for specific data, data that fits a particular, specific criteria. The where clause gives you the tools you need to get just the rows you're looking for, instead of the entire table. Selecting the world database here. In SID, and if we select everything from country like this, then we get the 239 rows with all of the data in them. This is a very rich table and it has a lot of information about each country.
For example, if you want to know which countries have a population under 100,000 it's easy to construct a query like this. Let's get the name and the continent and the population from country where population is less than 100,000, and we'll order by population descending. So when I press go, now we have a list of the name, the continent and the population in order of the population descending.
Just the countries with under 100000 of population. So there's forty rows returned, and we know now that forty rows in our table match that criteria. So there where clause takes a boolean expression. So this population is less than 100000, that's either true or false, that's called a boolean expression. Rows are returned only if the Boolean expression evaluates as true for that row. Now you'll notice that we're missing some countries from this list. We come down here towards the bottom.
You notice that we're missing some countries that really belong here, where the population is unknown or very small, countries like Antarctica, because those countries have null. In their population because it's an unknown quantity. That data was not available for the people who were creating this database. So, we add another expression to the where clause to include those countries. I'm going to start splitting this up into different lines to make this a little bit more readable here.
So put the from clause on one line and the where clause on the other line and this is SQL. You are allowed to do this as long as it's all terminated with that semi-colon. So my where clause now, I'm going to add an or population. IS NULL, like that. And when I press Go, we now get 47 rows. If we come down here to the bottom of the list, we'll see where there's a few with NULL, Antarctica, French Southern Territories, that's also in Antarctica.
Most of these are in Antarctica or in Oceania, and there's one in Africa, the British Indian Ocean Territory, in Africa. So those are the rows that have NULL in their population. So boolean operators, like this or, are used to bind different expressions together. Here I used and or operator to bind two boolean expressions, and I get the result when either of these two expressions are true. I could use an and operator to bind the expressions so that rows are returned only when both of the expressions are true. For example, instead of this or, I can say AND Continent = 'Oceania', and I press Go.
And now we just get the rows where the continent is equal to Oceania, and the populations under 100,000. So, we're only getting 16 rows now because both of those conditions have to be true, because they're bound together with the AND operator. The Where clause is used to filter results so you just get the rows that you want to see from your query. In the next couple of movies I'll show you some SQL operators that are specifically designed to provide additional power and flexibility for Where.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104268 Viewers
56 Video lessons · 116198 Viewers
71 Video lessons · 85415 Viewers
131 Video lessons · 40898 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.