If you’ve been following our tutorials, you have a good grasp of some basic SQL queries. This time, we’re going to cover three new queries. AND, OR, and IN.


📌AND: find information that matches 2 specific categories
📌OR: find all information that matches at least 1 specified category
📌IN: find information that matches any of the following categories

Okay, let’s go back to our film table. We have tons of information in it: the movie title, release year, languages it’s available in, rental duration, price, rating, special features, and even the replacement cost if a customer doesn’t return the movie (because we’re still renting out movies in this example and streaming doesn’t exist.😆)

Let’s say we want to look up all the movies that have two or more of those factors in common. How can we do that?

# Table of Contents

Query 1: Using AND
SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate=0.99 AND length>180;

Query 2: Using OR/IN
SELECT address_id, address, district, city_id FROM address WHERE district=‘Texas’ OR district=‘California’;

SELECT address_id, address, district, city_id FROM address WHERE district IN (‘Texas’, ‘California’);

# AND/OR/IN

First on our list is the query AND. It’s self-explanatory, but let’s define it for the sake of our building glossary.

📌AND: find information that matches two specific categories

Q1. Finding $0.99 films over 3 hours long (film_id, title, rental_rate, length)

Use AND to satisfy 2 conditions at the same time
SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate=0.99 AND length>180;

Let’s say we want to check the price of the movies in our stock. Our query might look something like SELECT film_id, title, rental_rate FROM film;
We can narrow this down to focus only on movies that cost 99 cents by adding WHERE rental_rate=0.99.

Final query:

SELECT film_id, title, rental_rate FROM film WHERE rental_rate=0.99;

This will give you a nice, tidy list of all the movies that are $0.99. But what if you want to see movies that cost 0.99 and they’re about 3 hours long? Easy! Just add AND.

Make sure to add “length” to your SELECT line to be able to see that information.

SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate=0.99 AND length>180;


🔑 Notice how we didn’t write “length=3 hours”. SQL won’t understand that because that’s not how the information was input into our table. Computers process numbers much easier, so we entered the time in minutes. We also want to avoid the equal ( = ) sign here. How many movies do you know that are exactly 180 minutes? That’s why we used the less-than sign ( > ) instead. This will give us a better estimate.

✦ Click SQL Run to start the query.

Tada! Here are all the movies that are about 3 hours long and cost only $0.99!

Q2: Finding addresses in Texas or California in the Address Table
(address_id, address, district, city_id)

OR: Satisfying one condition at least (conditions can be located in different columns)
SELECT address_id, address, district, city_id FROM address WHERE district=‘Texas’ OR district=‘California’

Next on our list is the query OR.

📌OR: find all information that matches at least 1 specified category

Let’s take a break from the film table and check out the address table. Try this query and take a look at the new content: SELECT * FROM address;

The results aren’t too complicated. We only have nine columns this time:
address ID, address, address2, district, city ID, postal code, phone number, location, and last update.

If we want to look up the addresses located in Texas and addresses located in California, how could we structure the query? First, we need to specify what we want to see.

Let’s start with:
SELECT address_id, address, district, city_id FROM address;

We can use WHERE to find addresses in a specific district. Your first instinct might be to write WHERE district=‘Texas’ AND district=‘California’; but as we already covered, AND means that both conditions must be met equally. That means SQL will think you’re searching for one address that is located in both Texas and California. You probably won’t get any results. So we’re going to use the query OR!

SELECT address_id, address, district, city_id FROM address WHERE district=‘Texas’ OR district=‘California’;

⚠️ Remember to use single quotations around the district names to tell SQL that we are not dealing with numbers. ( ‘ ’ )

Another way to find this information is by using the IN query. Using OR could make your query long and complicated. For example, if you want to look up the addresses in Texas, California, Seoul, and Japan, you need to type out “district=” 4 times.

Since we’re here to make things easier for you, lb net’s use IN.

📌IN: find information that matches any of the following categories

IN: Satisfying one condition at least (conditions are located in same columns)

ex.) WHERE ‘column’ IN (condition 1, condition 2, condition 3, …)

SELECT address_id, address, district, city_id FROM address WHERE district IN (‘Texas’, ‘California’)

This query is going to be very similar to our last one. Start with SELECT address_id, address, district, city_id FROM address WHERE district like we did with OR. But instead of writing “district=” we’re going to make the search broader. Erase the equal sign ( = ) and write IN followed by the locations you want in parentheses. It’ll look like this:

SELECT address_id, address, district, city_id FROM address WHERE district IN (‘Texas’, ‘California’);

⚠️ We still need to use single quotations around the district names to tell SQL that we are not dealing with numbers. ( ‘ ’ )
✦ Click Run SQL to start the query.

You’ll get the same results in a much cleaner query.

🔑 If you run both queries (making sure to end both with a ;) then QueryPie will show you both results side-by-side!

# Practice Time

Time for another scenario!

📰 We’re back in the movie store, and you’re working diligently behind your computer. You’re working so hard that you don’t notice the customer talking to you until he has repeated his question a second time. He’s asking you for some movies that he can rent - but he has a rather specific condition. The movie rental period of the movie must be less than three days or more than five days. How can you look up this information?

Here is the answer! Take a look!

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration NOT IN (3,4,5) ORDER BY rental_duration, rental_rate ASC;

Here we used ‘NOT IN’ to exclude the days our customer didn’t want. While IN is used for satisfying at least one condition, we can use NOT IN to eliminate the ones we don’t want. Instead of writing IN (1,2,6,7,8,9,10,11…), we can write NOT IN (3,4,5) to make life easier.

🔑 Bonus Tip

When you write multiple column names after ORDER BY, the order will sort the result you wrote the query. In our example, the data was sorted by rental_duration first and rental_rate second because we wrote it that way. As mentioned before, ORDER BY sorts data in ascending order by default, so you don’t need to write ASC… still, it’s good practice!