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:
For example, say you were a Data Analyst at Amazon, and were trying to find product reviews with low star ratings. You could query the reviews table, and filter it down to just reviews with less than 4 stars using the SQL clause :
Try this snippet 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:
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 .
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.
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:
Let's practice with in the next practice exercise.
Now it's your turn to practice WHERE by finding all 3-star Amazon product reviews.
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:
|Example in Query
|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!
AND, OR, NOT ❌