Welcome back, everyone, to part 12 of topic three in our database class. In this video, I'm going to talk about how you can use the keywords end and or to create compound conditions in our SQL statements. Next, we will learn about some keywords that we can use to create compound conditions. So we can check more than one thing in our where clause. So we're not just saying, I only want people who work in, I don't know, department number two or department number one or whatever, but I can use this to establish a range of values or check multiple things. So the first of these, what we can use to construct compound conditions is the end keyword. So this then, as it says here, represents an intersection of the data sets. When we first started learning about SQL, a few lessons ago, I told you how it was based on the notions of relational algebra. And that we can represent those things with a then diagram. So imagine that we have a then diagram here and end is the intersection. So it's this area of overlap between them. So if each of these circles represents a condition and we use an end, then the only result that will be returned is where both of the conditions are true. So if this is condition a and this is condition b, then to be included in the results of our SQL statement, a particular row must satisfy both condition a and condition b, which is represented here by the overlapping area in this Venn diagram. Now I'll just take these out, label them outside of the circle for the sake of the next little demonstration here. So this is condition a and condition b. So if we use an or statement, then instead of representing an intersection, what we're representing in terms of relational algebra is a union. And an or is an interesting concept because what it means is that if condition a is true, that's not here with these kind of blue lines. And note that to this area here is also part of condition a being true. So if a is true, or if b is true, which I'll draw here with, I don't know, let's do green. So this is condition b, right, if b is true, then it'll be over here. And as before note that this area here is also part of B. And so if a or b is true, or a and to be here true, which is the intersection at the overlapping area, basically anything up here, anywhere, any of these things is true, then that row would be included as part of the results. So if a is true, or if b is true, or if both a and b are true, which in our case is represented by this area here, middle, this is a and b, then it would be included in the results. So let's play around with some of these and see some live examples. And to begin, let's let me include the higher date out here. So we have a little more data with which to work. All right, now, just leave that for now. So here are all of our data. We can see that we have total of what it's like about 12 employees in our database. They work in departments one through four and they're hired and see anywhere between 2018 and 2021. So let's play around with some of these compound conditions now. I'll put my wear back in here and we'll say, I'll say where the department ID. Department ID equals two. So we've already seen something like this. And you can see here that we've got our results, but let's say that we only want people to work in department. ID number two, who were hired in 2020 or later. So we can use the end keyword. And then we can specify an additional filtering criterion to limit the results to what we want. In this case, we want people that were hired on or after January 1st, 2020. Now, if we look at these results down here, we can see that we should only have two rows in the results. Right, because these are the only two that would meet both of those conditions. So let's run this and see if it works as expected. And it does. Right. So you can see here in our results, both PETA and Hey, Mitch work in department number two, which was our first criterion. And they were also hired on or after the 1st of January 2020. We look at their higher dates and we'll see that that's true. Now, if we change this to an or. Then what will happen is if this condition is true by itself, the associated row will be included in the results. If this condition is true by itself, the associated row will be included in the results. And if both of these conditions are true, then the associated row will be included in the results. So that's, let's going to happen here. So by changing the end to or let's see what happens. Execute this now. And of course, I get many more rows. You can see I get nine rows in my results. So everybody here either works in department two or they were hired. On or after the 1st of January 2020 or both. Right. So if I look through any of these, I can see, okay, this person works in department two and they were not hired. After 2020, but they're still included in the results because they work in the department. Same with this person and this person. This person meets the higher date criterion. They were hired on or after January 1st 2020, but they work in a different department. Nevertheless, they're included in the results because we used an or up here, rather than an end. And so on and so on. So we can see that everybody here now, all of these nine records meet one or both of these two criteria. They either work in department ID number two. They're hired after 2020 or both. And so that's the difference between and or. So really quickly building out our set of capabilities here. What else could I show you? Oh yeah, you can use parentheses. So I could do something like this. And that makes that to treat it all as one condition. And then I could extend it maybe by adding I don't know like an additional end. I think we have employee ID in here. Same employee ID is I don't know less than six. Okay. So the only way now that a row will be included in the results is if this is true. So everyone must have an employee ID less than six to be included in the results. And one of these conditions is true or both. So if I run this you should see we get fewer rows. Let me include the employee ID just something that we can see that this is behaving the way that we expect it to. So we can see our three of the three rows that are in our results are employee IDs one three and four. Which meets this criterion. Right. And then to be included they must also meet this criterion or this one or both. So they're either going to be in department number two in this case they're all in department number two. And surprisingly none of them was hired after 2020. So it's there working in department number two that is qualifying them to be included in our set of results here. Okay. So you can imagine you can basically once you know those end. And or operators and all of the six comparison operators equal to not equal to greater than less than greater than or equal to and less than or equal to. You can create basically any kind of combination of filters that you need to limit the results. That you're getting from your SQL select statement. So these are all they're all part of the tools that that you need to ask the database. Right. And you can imagine that's just once you learn the pattern you can arrange these in any sort of combination that meets your needs for whatever sort of question you're trying to ask. It's continue. So here we just see some examples of or and end being used. We already saw live examples of these so we won't spend too much time out here. But this is telling us that hey if somebody works in a department that is less than seven. Which would be say departments one through six. Or if they happen to work in department number 12 then they will be included in the results down below we see the use of an end. Right. So if they work in department number nine. And they were hired after the first of January 2021 then they would be included in the results. Right. That is both of these conditions must be true in order for the entire where statement to to result as the true. Right. So both of these must be true in order for a row to be included in the results if we're using an end. So if we're using an or then either condition can be true even if the other one is not or both conditions can be true. In this case it seems unlikely that there's any overlap between these two conditions right because it doesn't seem possible that someone works in a department less than seven and also department 12. So one of the things that we can do then is build out some pretty complicated where statements. Right. So let's imagine that we want people that work in departments. I don't know let's say a two three or four. Okay. So I could do that with what we already know. By using something like this. equals three. Or. I d equals four. And I'm spacing. Open up a little more real estate for us here. Okay. So we already know how to do this. I could just say if I want only employees who work in departments two three or four I could build. this sort of chain of filters using a combination of or statements. And if I run this of course we'll just get employees that work in departments two three or four. I know what he is up here who works in department one. It's all just two three or four. So that's fine. But there's a much cleaner way that we can accomplish the same thing. And I'd like to show you that because it appeals to my natural laziness. And one of the things about laziness is that you can use you can rely on laziness to find the most efficient way of doing things.