SQL WHERE Tutorial With Examples
The SQL clause can be combined with the statement from the previous lesson to output only certain rows, based on if they meet certain conditions:
SQL WHERE Example
For example, say you're a Data Analyst at Amazon and were trying to find product reviews with low-star reviews. You could query the table and filter it down to just reviews with less than 4 stars using the SQL clause :
Try this query yourself by copy-pasting the above SQL query into this Amazon Data Analyst SQL question. You'll see that the SQL code successfully filters down the input table to just 3 records:
SQL WHERE with Multiple Conditions
But, what if you need to refine your query further by applying multiple conditions?
Syntax:
Now you're tasked with finding low-star reviews for a specific user. Let's say you want to find reviews with less than 4 stars for user ID 362:
Run the query in the Amazon Data Analyst SQL question.
SQL WHERE vs. Excel Filter
In case you found the SQL WHERE example confusing, maybe you'll find the similarity to Excel and Google Sheets's filter function helpful?
For example, say you had the same dataset of Amazon product reviews as before, but loaded it into Google Sheets. If you wanted to only get reviews with less than 4 stars, you'd filter by condition and manually input "Less than 4":
The beauty of SQL is that instead of putzing around in the complicated Google Sheets UI to filter the data, in SQL you can just write it down explicitly with .
Why Use WHERE Queries
Besides narrowing down resulting rows in a SELECT SQL query, the clause is important because it speeds up a query, by avoiding the processing of extra data. It also helps your database servers use less resources, which is quite important in large-scale data warehouses with billions of rows, where a simple could rack up a $1,000+ AWS or Azure bill.
WHERE with =
The statement is versatile – you don't just have to use it with the less than operator!
Let's try it with equals operator (), so that we can get all Amazon product reviews where the product being reviewed is 12580:
The resulting output from the above query would look like this:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 2501 | 142 | 06/21/2022 00:00:00 | 12580 | 5 |
| 4582 | 562 | 06/15/2022 00:00:00 | 12580 | 4 |
Let's practice with in the next practice exercise.
WHERE Practice Exercise
Now it's your turn to practice WHERE by finding all 3-star Amazon product reviews.
Using Logical Operators
The clause is often used with other logical operators like (equals) and (not equals) to filter down rows. Here's a table of all the logical operators you can use:
| Operator | Definition | Example in Query | Interpretation |
|---|---|---|---|
| = | Equals to | field_name is equal to 2 | |
| !=, <> | Not equals to | field_name is not equal to 5 | |
| <, > | Less than, more than | , | field_name is less than 5, field_name is more than 10 |
| <=, >= | Less than or equal to, more than or equal to | , | field_name is equal to or less than 5, field_name is equal to or more than 2 |
For example, if you want to get all 4-star reviews AND make sure they are NOT for product_id 50001, you'd write:
Notice the keyword - it's ✨NEW✨?
AND allows you to combine multiple conditions, and is the topic of our next lesson: SQL AND Tutorial!
Next Lesson
AND, OR, NOT ❌