Online College Courses for Credit

+
Multiple Filters

Multiple Filters

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses AND and OR to combine filtering of conditions.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

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 being 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)%';

File:11217-1170-1.png

If we changed the criteria of finding the 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 of the 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:


File:11218-1170-2.png

We can have as many criteria as needed to compare. 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:


File:11219-1170-3.png

In essence, the query would first take the customers and retrieve those in the USA. Then in that subset, it would find those that had the support_rep_id equal to 3. Next, in that subset, 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.

File:11220-1170-4.png


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';

File:11221-1170-5.png


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

File:11222-1170-6.png


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';


File:11223-1170-7.png

This behaves differently from the AND operator, where 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. It is possible that an OR operator could have both conditions returning rows that would be returned in the other options. 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;

File:11224-1170-8.png


However, running each independently would return the same rows:

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

File:11225-1170-9.png

SELECT *
FROM employee
WHERE reports_to = 6;


File:11226-1170-10.png


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';

File:11227-1170-11.png


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

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

File:11228-1170-12.png


Then we would combine it with:

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


File:11229-1170-13.png

This would return different results than if we did the OR operator 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:


File:11230-1170-14.png

As you will 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.

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.