In this lesson we’ll learn how to use the SQL WHERE clause to filter your results. In prior lessons we have talked about using the SELECT statement to return one or more columns, but always it returned every row. What if we want to return a limited set of rows? How can we filter our results?
All of the examples in this tutorial are based on PizzaDB. You can get the script to build the PizzaDB here.
Luckily SQL has an answer to this question. To filter rows you can use the WHERE clause.
Here you can see where we select every Product having a ProductType equal to ‘P’
This statement will only show rows where ProductType = ‘P’. In other words, in order to show a row, the SQL Where clause must be true.
SQL WHERE clause Syntax
SELECT column1, column2, ...
FROM TableName
WHERE condition;Here condition is a comparison that is made for each column. If the comparison is true, the row is included; otherwise it is filtered out.
| Comparison | Description | Example |
|---|---|---|
| = | equals | ProductType = ‘S’ |
| > | greater than | Price > 10.00 |
| >= | greater than or equal to | Price >= 4.50 |
| < | less than | Price < 10.00 |
| <= | less than or equal to | Price <= 4.50 |
| <> | not equals | ProductType <> ‘I’ |
Other than the addition of the WHERE clause, the basic SELECT syntax remains unchanged.
Using SQL WHERE Clause Conditions
Let suppose you need to list all Products that are ProductType ‘P’.
Example using Characters
Use the not equals operator to filter out ProductType P values from your query results.
Notice that when we compare character values, we place them in single quotes (‘).
When working with numbers we don’t need to do this.
Example using Numbers
Find all products have a price greater than or equal to 10.00
When comparing numbers, we don’t need to place them within single quotes.
Exercise
Now that you’ve learned more about the SQL Where clause, see if you can find all products whose product name is ‘Small Pizza’