Video Transcription

Welcome back everyone. So in this video, we are going to take a look at the offset fetch filter. So the offset fetch filter is the anti standard, equivalent to talk. So before we cover top, but top is going to only be specific to a certain set of database management systems. A SQL Server, for example, supports the top command. But not all, SQL is created equal. But offset fetch is standard. So you’ll find it in the majority of database languages that implement SQL. So that’s a benefit there. But the there is a little bit of a difference here. So top itself doesn’t actually support an offset. Although as offset fetch sounds like it does support that so we can offset by in number of rows. So if we want to skip a certain set certain number of rows, we can do that with the offset. And then we the fetch part is more similar to the top command, where we fetch n number of rows from our SQL results, right? Again, though, this is a filter that is determined that is based off of the ordering of our results.

So this happens after the order by if the order by is present in your query. Otherwise, the offset fetch and top filter the rows on the order that they rows actually appear in your database. Now, the syntax here can be kind of wonky to read. But more or less, the extra syntax here is primarily just for readability. So you see that we have row rows first, next, these are purely just for readability. And just give you some flexibility in writing your SQL. So for example, if you have, if I want to offset by only a single row, it’s kind of weird to read offset one rows. So you can actually right offset one row and first versus next. First and next are completely interchangeable. So fetch first 100 versus fetch next 100 does the exact same thing kind of just depends on the user’s preference of which one to actually use. But let’s take a look at a few examples of offset fetch. And we can kind of compare that to what top would do. So in this example, here, we have order ID order date, and customer ID as our columns, all from the sales orders table, and then we’re ordering by the order ID and fetching offsetting offset by zero, so we’re not skipping any rows, fetch the next 1000 rows only.

And so the next 1000 rows gives us the the next 1000, or the first 1000 customers are the customer sorry, the first 1000 orders. So if I replaced, again, if I replaced next, with first, functionally, those are identical as far as the results go. So depending on which one makes more sense for you, and you can use first or use next, both can be used interchangeably. Now, let’s go ahead and let’s say I wanted to skip the first five orders, for some reason, right? I can actually do an offset there. So you now see that my query, my query results actually start producing rows starting at order six, because we skipped the first five. Now, this functionality is not something that we can achieve with the top filter, top is top is able to do this. So I could just do top 1000. But I cannot achieve this functionality with the top command which is this, which is why offset fetch in some ways, can be a little bit more superior of a command to use. Now fetch and its own is optional, right fetch by its own is optional. So I can actually take out our fetch. And by the way, the dash dash is the document or the way you can document your code and SQL. So dash dash, that that text after the dash dash is ignored by the SQL compiler. But if we run this now, notice, we get, we get same query out, we start at order of six.

But now instead of getting only 1000 orders, I’m actually now pulling all of the orders after after order five, so order six and on instead of order six, to 1006. So that is the offset and fetch. And we will be using offset and fetch and top in a variety of ways as we start getting into some more complicated queries later in the course. But let’s do a quick review here of what we have covered so far. So we covered most of what we would see an enormous possible on a select statement. So we have SELECT FROM WHERE group by having order by offset and fetch. Now, inside of the select, we have distinct and top top in particular, is going to be unique, somewhat unique to Microsoft SQL Server, it is not standard, but everything else you see here is as part of the SQL standard. Now, as just to kind of drive home the processing order again, alright, our unlike our Python code or Java code, we don’t execute our query from top to bottom right, we are executing it from a logical processing order. So even though we are required, with the SQL syntax, to go select from where group by having and so on, we cannot change up the ordering there.

But it can be a little bit hard to get used to writing queries in this way, because the logical processing order is the order in which the data is actually utilized. So that really kind of kind of impact your results a lot. Depending on which statement actually gets executed first, this will become even more apparent the need when we start covering how we join tables together. So from happens first, just as a review, then our WHERE clause, so we select the data first, so the data source, so which table or tables that we want, then we can filter the rows out of that table that we don’t want, we can optionally group those rows together by some condition, then we can actually filter the groups and remember, the where filter is row by row the having filter is group by group, and they cannot be used interchangeably. Then after our having clause, our select clause will be processed. And along with the SELECT clause, the distinct clarifier will actually be executed along with that, because remember, by default, all is all is the all is the behavior of this the default behavior.

So all rows will be included, unless we specify that we only want distinct rows. Then, after the sinks, we order so once we have projected the columns that we are actually going to see in our results, then we can order our results by certain columns. The benefit here in the order by is that we can actually utilize the column aliases that we we define inside of the SELECT clause, and then top or offset fetch. Now offset fetch is not a separate operation. Offset fetch happens alongside the order by so order and then offset fetch happen at the same time. It’s part of the same clause. But top is a completely logically separate clause that actually gets executed differently. After completely after what the order by the ordering still, if you have offset fetch, the ordering still happens first. But logically, it’s all in the same actual part of the execution. And then I’m not going to cover this too terribly much. But you can see all of the, but you can see all of the syntax that we’ve covered so far with our basic single table queries, and we’ll be utilizing this syntax throughout, really a lot of the course We are only going to introduce more and more to this especially where we have from groupbuy where we can join and select from multiple tables grouped by multiple things and actually introduce more complex expressions