SQL AND, OR, NOT Tutorial With Examples

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:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE. The operator displays a record if the condition(s) is NOT TRUE.

Let's dive into each SQL logical operator more deeply.

The SQL AND Operator

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.

AND in SQL vs. AND in Google Sheets

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!

Multiple AND Operators

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.

You'll notice that all three of the requirements are satisfied in the output:

SQL WHERE AND Practice Exercise

Let's practice using WHERE and AND to filter Amazon reviews based on all 4 of these conditions:

1. the review should have 4 or more stars
2. the review ID is less than 6000
3. the review ID is more than 2000
4. the review can't come from user 142

Filtering the reviews data on the above conditions should give us the following result:

SQL OR Operator

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 ().

Multiple OR Statements

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:

SQL AND OR Practice Exercise

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:

• the start count is greater than 2, and less than or equal to 4
• the review must come from either user 123, 265, or 362

Here's an example output which matches the above conditions:

SQL NOT Operator

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? 👇👇

NOT BETWEEN

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.