Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

Multiple Filters

Author: Sophia

what's covered
This tutorial explores using AND and OR in the WHERE clause of SELECT statements to combine filtering of conditions in three parts:
  1. Using AND
  2. Using OR
  3. Combining Both

1. Using AND

The AND operator displays a record if all of the conditions are separated by AND are true. You can think of this approach of using the AND operator as if you were searching for a car to rent. You may be looking for a four-door, with the color blue, having leather seats, and so forth. This would be a great example of using the AND to ensure that we are considering all of the criteria.

If we wanted to search for customers from Manhattan in New York City, which is in the USA and has the area code 212, we would first need to identify the columns to search. The country column would contain 'USA' while the phone column would have the area code. However, it is important to note what comparison would be needed. Since the country is a specific value, we can use the equal sign to compare. For the area code, we would need to use LIKE with wildcards:


SELECT * 
FROM customer
WHERE country ='USA'
AND phone LIKE '%(212)%';

Query Result Example

If we changed the criteria to finding American customers who spoke with a particular support representative, we might query records that have the support_rep_id equal to 3 and the country USA. We can change the query as follows:


SELECT * 
FROM customer
WHERE country ='USA'
AND support_rep_id = 3;

This would return a result set as follows:


Query Result Example

We can have as many criteria as we want. If we extended the prior criteria to include those with the city starting with the letter C, we would adjust the query like this:


SELECT * 
FROM customer
WHERE country ='USA'
AND support_rep_id = 3
AND city LIKE 'C%';

This would return the following result set:


Query Result Example

In essence, the query would first take the customers and retrieve those in the USA. In that subset, it would find those that had the support_rep_id equal to 3. Next, it would retrieve those that had the city that started with the letter C. You can think of this as the intersection of all of the criteria being returned.

Venn Diagram Graphic


2. Using OR

The OR operator displays a record if any of the conditions separated by OR is true. As long as one of the conditions is met, the result would be returned. If we wanted to get employees that had the title of IT Staff or those that had the title of IT Manager, we could run it as two separate statements like this:


SELECT * 
FROM employee
WHERE title = 'IT Staff';

Query Result Example



SELECT * 
FROM employee
WHERE title = 'IT Manager';

Query Result Example


Using the OR operator, we have the ability to combine the sets together like this:


SELECT * 
FROM employee
WHERE title = 'IT Manager'
OR title = 'IT Staff';


Query Result Example

This behaves differently from the AND operator. With the AND operator, the more times we use it, the smaller (or the same) the result set becomes. Using the OR operator, the more times we use it, the larger (or the same) the result set becomes. If we look at employees that either have the title IT Staff or reports to 6, the query would look like this:


SELECT * 
FROM employee
WHERE title = 'IT Staff'
OR reports_to = 6;

Query Result Example


However, running each independently would return the same rows:


SELECT * 
FROM employee
WHERE title = 'IT Staff';

Query Result Example


SELECT * 
FROM employee
WHERE reports_to = 6;


Query Result Example


3. Combining Both

We can also combine both types in a single query. It is important to note that the AND operators are performed first, and then the OR statements are executed. In the following query:


SELECT * 
FROM employee
WHERE title = 'IT Staff'
OR reports_to = 6
AND phone like '%1';

Query Result Example


We would first get the result set of the AND statement as if it were:


SELECT * 
FROM employee
WHERE reports_to = 6
AND phone like '%1';

Query Result Example


Then we would combine it with:


SELECT * 
FROM employee
WHERE title = 'IT Staff';


Query Result Example

This would return different results than if the OR operator were performed first. If we did want to have the OR operator first, we would use parentheses to form complex expressions. The query would look like this:


SELECT * 
FROM employee
WHERE (title = 'IT Staff'
OR reports_to = 6)
AND phone like '%1';

This would force the query to do the OR statement first and then find the intersection between that result and the phone like ‘%1’. This statement would return:


Query Result Example

As you see, you can customize the result set through the use of filters with the AND and OR operators while using parentheses to control the ordering.

Video Transcript

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
We can use AND and OR to combine the filtering of various conditions.

Source: Authored by Vincent Tran