Video Transcription

So now that we have actually worked a little bit with select and from So specifying which tables we want to pull data from, and which columns from those from those tables we want. Now we can actually filter which rows that we want. So the from clause is again, right the selection operation, right. So it is all of the rows, all of the rows of data that we want to pull as part of our query. Now, select does filter a little bit, but it filters only vertically. So it filters which columns actually end up showing up. But to filter which rows show up, we need the where clause, the where clause, as I mentioned, just provides the basic filtering per row. And now it will accept any predicate or Boolean expression as part of it. So all of the Boolean expressions that you’ve learned so far in just your basic programming, will actually apply here, for the most part. So we are partially supporting the selection operations.

So from gives me all of the rows from a particular table or tables. And if I ever want, if I want to reduce the number of rows in that set, I use the where clause. So I’m filtering out which rows actually end up showing. So let’s take a look at a few examples here, a little bit easier to actually see this in action, rather than listening here to me talk about them. So let’s add a new cell down here. So as we saw before, from our, our larger query, so if I run that again, right, we have large 1000s and 1000s of rows there. So how do we actually reduce that to be only a specific set. So if we take this same exact query that we had, right, and then I’m going to add to this a where clause. So select from where, and then I’m going to say year, O dot, order date, and then set that is that equal to 2016, since we are only doing orders of 2016. So now, if you run that and look at the order date, so nice, my the number of rows that actually have here are significantly fewer. And my order date is only 2016. orders that have a year of 2016. So the Boolean operations are very similar to what you would expect in Python or Java. Of course, now I’m not using the double equals for equality, I’m using the single equals which can be confusing. In this context, when used in the where clause, it is not an assignment operator, it is the equality operator for the UI for a Boolean expression.

Now, years are kind of tricky. What I have here is a specific order date, but I actually have to convert the way I’m actually writing this code, I’m converting each time each date, so year, month, day, to a single year. So I take the full year, convert it into a car full date, and then convert it to just a year. But I can do this exact same thing down here by just doing a range on the date instead of having to convert it. And typically that is going to be the preferred way of doing so because it is a little bit more efficient. And with databases, unlike our our code. We want our code to be efficient. But it is more important for SQL queries to be efficient as possible. Because we’re dealing with 1000s upon 1000s of records, the majority of time, think about, you know, writing queries for something like Amazon, right has millions upon millions of things of records there. And so if we have an inefficiency in one of our queries, that adds up to a significant amount of extra processing time over some period on our servers, and of course, a worse experience for the our our end user. So we do want to be as efficient as we possibly can. So let’s go in here and say order date. And then we are going to do, we’re going to use like a greater than or equal to here. So and I’m going to put the date here as a stream. And so this is an easy way to do this, this doesn’t have to be an official date time datatype as long as the string matches what we’re actually looking at, so we’re doing one one of 2016, so January 1 2016. And then we are going to put an AND operator here, so and ODOT, order date, order date, and you’ll find the IntelliSense with SQL is hit and miss that time.

So whether you’re in your Azure Data Studio, or SQL Management Studio, or whatever your IDE you’re using IntelliSense can be hit and miss, which is what’s happening there. But anyway, so let’s, let’s put, we want our, our date to be less than 2017. So we’ll put the first of January of 2017. Now I could I could put the end of 2016. And do make sure make this less than or equal to, that also would have worked. And then we’ll close that off with our clause. See here, that’s where my mistake was I had an extra semicolon, semicolon, by the way, as I’m showing you here, denotes the end of a SQL statement. Okay, run that. And there we go, I get all again, all of the orders that were made in 2016. But this is actually a little bit more efficient than the query that I showed previously, where I’m converting the date to a year and then comparing it to the number here, I just compare the date directly without actually modifying its format. And I’ll be showing a variety of these little things as we’re working through our examples here and through assignments. The code I’m the sequel that I’m using here is a very basic WHERE clause write this using a date, but your where clause is essentially used on any column, that that is being available or projected from our slot, right. So whatever columns are available, I can actually pull them out there. So I’ll actually show some of the orders or some of the sets there, but it’s not specific just to the select clause right? The from operates first, then the where and then the select.

So the rows are filtered before they make it to the select. So the selection operation happens with the from and where clause is done before projection. So selection first, then projection. So select actually happens after the from and where clause SQL statements. But here I just used simple equality check. And greater than less than, but there are a lot of different Boolean operators that we can utilize inside of our WHERE clause, and a variety of other places in our SQL statements. booleans though are the only are only supported as expressions. So there is no actual boolean data type. So you know, in Java, we have Boolean. And even in Python, we have a false and true type associated with the language, but SQL really doesn’t. They just use it as expressions. And that’s the vast majority of database management systems. So SQL Server, MySQL, and a variety of others will have very similar similar goes. So where can we use these Boolean expressions. So we’ve already seen them being used in WHERE clause in my examples. But we also have if statements and loops inside of inside of our SQL statements, and we can also have like case a case function, which will I’ll showcase here in a later video.

So the case function is very similar to the switch statement in Java. Although of course, Python does not have a switch statement, but more or less just a shorthand series of ifs. But we’ll get to that here. And not too long, but all the operators that are are all the primary operators that are supported for Boolean are mostly standard except as I mentioned, as you see here, and in my previous example, the equality operator is not enough. equals, it’s just the single equals, we have greater than less than or equal to naught is done a little bit different. So the standard way of doing not is less than greater than. But there are others that are supported like the exclamation points. Okay. So not equal to, not less than not greater than those are supported. But they are not part of the SQL standard. So your mileage may vary, depending on which database language, you’re actually using all SQL, but each, each company implements it in a slightly different flavor.

Most of your languages that we work with write a boolean value, all right, even if we talk about just general logic is true or false, right? There’s no in between. But with databases, we actually introduce a third value called unknown. Unknown, that’s kind of a weird situation, right? Because what happens, if a value is no, most languages know is going to come back as false or false see, because no being the absence of value, the absence of value cannot be true, because there’s nothing there, which is a lot of the same case in a lot of languages. But with SQL, as you’ll see here, we are going to pull a query like this one sec, let’s clear. Clear that there and run this. So again, write slug star, I’m using Select star, just as a quick example. Try not to get into the habit of using Select star for solutions to things, it is very useful tool to just kind of explore results. But at the end of the day, you’ll want to reduce that and actually specify your columns. But if I run this here, you’ll see that nothing gets returned. Right? Nothing gets returned. Because nothing is no All right, the order date does not actually know. But at the end of the day, right? A lot of the times here, this is going to be still evaluate to true or true, false or on known. Right? So even if it is unknown, right? It’s not going to actually show up.

So if I showcase this here, with this query here, the where clause is filtering the rows by order date, order dates that are not No, right, that are not equal to no. And running these, I still get zero rows, right? Because date, order date is actually a non nullable column. So the order date must exist. But a better way of actually showing this, because there’s also there’s seven like 73,000, some odd 100 rows and the orders. Table. But let’s switch this to a a column. That is no. Okay. So if I flip this back, right. Not no. Oh, dot internal comments, not No, I get nothing as a result. But if I flip this, right, say equal NULL, also, I the results are nothing right, and nothing is actually coming out. But if I flip this to say, is no. I actually get quite a lot of records out. So if I scroll over here, and ternal comments, right? So here you can see internal comments. All of these are actually not no sales orders that have no internal comments. But you notice that the equal sign and the not equals operator, both of those don’t actually work for naught because the Boolean comparison here, all right, a value so no, not equal, no is actually unknown, because we have unknown there so it’s not actually true. So don’t add so those items never actually get returned as a result in your query. So if you are ever working with no or a which is usually the case for for things that are non null or nullable columns. And if you’re trying to check for null, the is operator is usually the preferred way to do the Boolean comparison.

So, this will return true if the internal column comm internal comment is no right. So, if we backtrack this, I can say is I can also say is not no. Right? This is more more so related to Python than it is and how things are compared to Java, right? So we say is, is no, or is none in Python. And instead, and we also have an actual better pit person here. So, we actually have, we can actually showcase this one here, since this is columns are here. So we have a whole bunch of normal columns and we can show those that are not no there. So, these orders have already been picked, the items have already been picked up. So, that’s just another example of how we can utilize Really N expressions. Like said Boolean expressions most commonly are going to be found in your where clause, but as we saw back here on our slide, we can find them in our where clauses, control statements like ifs and loops, as well as our as a case function. You may also see them in a variety of other ways in stored procedures as well. But that will conclude this part. In the next section, we will talk about grouping or group by