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)%';
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:
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:
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.
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';
SELECT *
FROM employee
WHERE title = 'IT Manager';
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';
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;
However, running each independently would return the same rows:
SELECT *
FROM employee
WHERE title = 'IT Staff';
SELECT *
FROM employee
WHERE reports_to = 6;
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';
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';
Then we would combine it with:
SELECT *
FROM employee
WHERE title = 'IT Staff';
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:
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.
[MUSIC PLAYING] As a data sets get larger in the database, you'll find that you need to utilize multiple filters to find the information that you're looking for. There we'll utilize the AND, and the OR operator to put together different conditions, so that we're able to limit the data.
For example, we want to look for the first 20 customers. So looking at the customer being less than 20, but we also want to ensure that all those individuals that we're looking at are from country, USA. So we'll type the two items together. And we should return the customer ID, all these are less than 20 and then the country, all of them are in USA.
In essence, this is the intersection of all those customers that have the customer ID of less than 20, being that would return 19 different records, and those that live in the USA. There might be many other customers that live in the USA that have the customer ID that's greater or equal to 20. The other option is utilizing the OR.
If we select the OR, we're finding the union between these two sets of data. So if we run this query now, it know that it's showing 28 records. If we only looked at the customer ID of less than 20, we only have 19 records. If we're looking only at the ones in USA we have other records that go beyond that. So you'll notice as part of the customer ID, it should show at least 1 to 19 first of all.
And then all these other individuals that have customer ID that's greater than 20 have the country being USA. And so being that it finds that union between the two, it provides us the information of having multiple different options in this case, to be able to combine those data sets together.
[MUSIC PLAYING]
Source: Authored by Vincent Tran