1. Restricting data
In the final video of this chapter, you'll learn how to filter data.
2. Filtering rows
Usually, you don't want to return all the rows in a table. There will be specific information you need to extract. To do this you'll use comparison operators, comparison keywords, and logical operators.
Imagine, for example, that you only want to display data of customers that live in Portugal.
3. WHERE
For this, you need to use the WHERE clause, which is placed underneath the FROM clause of the query.
This query, for example, retrieves the first name, last name, and country of only those customers who live in Portugal.
Note that strings and dates need to be enclosed by single quotation marks.
4. Comparison operators
You can also filter on numeric values or dates. Additionally, there are different operators you can use to compare two values. You can check if a value is greater than, greater than or equal to, less than, less than or equal to, equal, or not equal to another value.
Let's look at an example. Most tracks are priced at 0.99 but a few are not. You can filter on rows with a unit price that is not equal to 0.99 by including the expression shown on the slide in the WHERE clause.
5. Comparison keywords
There are other comparison keywords available which are used to enhance the search capabilities of a SQL query. These are BETWEEN, IN, and LIKE. Let's take a closer look.
6. Comparison keywords - BETWEEN
You can display rows based on a range of values using BETWEEN. The range that you specify has a lower limit and an upper limit.
The query shown here returns rows from the Track table for any track that is between 100,000 and 200,000 milliseconds long.
7. Comparison keywords - IN
You can use IN to look for values in a list of values.
This query displays employee IDs, last names, and titles for all employees whose ID is 4, 5, or 6.
IN can be used with any data type.
8. Comparison keywords - LIKE
You may not always know the exact value to search for. LIKE can be used to select rows that match a character pattern. Two symbols can be used in the search string. The percentage sign represents any sequence of zero or more characters, the underscore represents any single character.
9. Comparison keywords - LIKE
For example, let's look for the names of all employees whose last names have the letter a as the second character. The underscore represents any single character at the start of the string, then the letter a follows, and then the percentage sign, representing any sequence of characters.
There are two employees with last names that satisfy the condition.
10. Logical operators
You can use the logical operators AND, OR, and NOT to do some more advanced filtering. These operators can be used in the WHERE clause to combine more than one condition.
11. Logical operators - AND
In this query, only invoices that have Australia listed as the billing country and have an amount of more than $4 are selected.
There are three invoices that satisfy both conditions.
12. Logical operators - OR
In this example, only invoices that have Australia listed as the billing country or have an amount of more than $4 are selected.
Since only one of the conditions needs to be met, more than three invoices are returned.
13. Logical operators - NOT
NOT can be used to reverse the result of a condition. This query displays the last name and job ID of all invoices that have a billing country outside of Australia, Canada, or the UK.
14. Recap
So to recap, WHERE can be used to filter rows based on a condition.
In the WHERE clause you can compare data using different comparison operators, the comparison keywords, and logical operators.
15. Let's practice!
Let's get to work!