In the last lesson, we showed you how the clause can be used to filter rows. In this tutorial, we'll show you how the keyword can be combined with , , and operators, to help you further fine-tune which rows you'll select using multiple conditions.
By the end of this tutorial, I hope you can understand my corny SQL t-shirt:
At a high-level, the and operators are used to filter records based on more than one condition:
Let's dive into each SQL logical operator more deeply.
AND is a logical operator in SQL that allows you to select only rows that satisfy two conditions.
For example, if you want to get all Amazon product reviews where the product reviewed had the ID 50001, and the star rating was greater than 3, you'd write the following query:
Here's what the output would look like – just a single row that matches both our conditions!
Try it out yourself by copy-pasting the above snippet and running it against the data in this SQL question from Amazon.
The SQL operator is similar to how AND works in Excel and Google Sheets, which returns or based on two or more conditions. To demonstrate this similarity, look at the Google Sheets example below:
Can you see how our desired goal to get reviews where the product_id is 5001, and the star count is more than 3, leads to in Google Sheets?
Do you notice how similar that is to the equivalent SQL clause: ?
As you can see, SQL doesn't have to be toooooo daunting to learn – it mirrors much of what you might already know from using Spreadsheet tools!
You can use SQL's operator with additional statements or any other comparison operator, as many times as you want.
For example, try running the below SQL query, containing multiple AND operators, on this Amazon SQL interview question.
Let's practice using WHERE and AND to filter Amazon reviews based on all 4 of these conditions:
Filtering the reviews data on the above conditions should give us the following result:
is a logical operator in SQL that allows you to select rows that satisfy either of two conditions. This is in contrast to the SQL operator, which requires a row to satisfy both conditions.
Try by running the below query against data from the Amazon SQL interview question:
By running the above OR query, you'll see that the first two rows satisfy the first condition (), and the third satisfies the second condition ().
Not only can you have multiple statements, but you can also combine it with too! Here's an example:
The query above looks for all product reviews with 3 OR 4 stars, AND then make sure the review_id is greater than 5000. Try running this yourself on the Amazon reviews dataset:
Let's practice using multiple s, along with multiple s, in this next SQL practice exercise which filters down Amazon product review data on the following conditions:
Here's an example output which matches the above conditions:
The operator in SQL is used to display records for which a condition is NOT TRUE. Here's what NOT looks like in action when querying a dataset of Amazon reviews and filtering it to reviews that don't have a 5-star rating:
This gives exactly the same results as using .
Curious then why we'd use , if could get us equivalent results? 👇👇
While has a ready-to-go negative complement of , there's many filtering SQL keywords that don't have a complement, so to negate the condition, you use the keyword .
For example, if you wanted to find all Amazon reviews where the star count is NOT between 2 and 4 stars, here's the SQL query you'd use:
We'll dive more into filtering on a range of values in the next tutorial on SQL BETWEEN.
SQL BETWEEN 🖖