Row value syntax is a way to compare multiple values to another set of multiple values. When it's just a single value, that is called a scalar value, but when you have multiple you can have this row value syntax which can be really powerful. A little bit confusing but still really powerful. Let me show you. The way that you do row value syntax is you open parenthesis and then you put your comma separated values in here.

And if we run this, you'll see, well, we misused it, But that is row value. So we're on the right path. So what you can do is you can say row value equals a row value. And if you run that you'll see, yeah, that's the same. But if you do that, that is not the same and so it evaluates to false.

Let's carry on with our null discussion from the last video for just one second and look at something interesting here. So 1, 2, 3 equals 1 null 3 and we get null because it's unknown and unknowable. If this were substituted with 2, it would be true. And if it were substituted with 4, it would be false. And so when it's a null, it's just like, I don't really know.

There's an unknown value there in the middle. But what is interesting is if you say 1 null 4, there's no chance. There's no freaking chance that these 2 could be equal even if you plug in a 2 there. Well, you still got a 4. You still got a 4 hanging out at the end.

There's no way that could be equal. So dealing with nulls just carries on. Nulls are everywhere in row value syntax if the null could be substituted for something that makes it match or something that makes it not match, then SQLite says, I gotta be honest. I don't really know. Alright.

Let's carry on to a place that this would actually be useful. So if we do select star from users limit 10. So we have our list of users here and instead of ordering by, instead of just ordering by the default ordering, we're gonna do, let's do order by first name, last name. So if we do that, then we see then we see all the Elias come first and then we order by last name. So we have Abbott, Abernathy, I don't know, Armstrong, Balistrier.

Anyway, lots of names in there. So, where we could use row value syntax is if we wanted to do, pagination that is a key set or key based cursor based pagination. Let me explain what this is. Cursor based pagination differs a little bit from offset based pagination. In cursor based pagination, the client or the user or whoever is consuming the data says, this is the last record that I saw and you send it back to the service and the service says, okay, this is the last record that they saw.

Let me show them the record directly after that. And so instead of saying, alright, order the records and then offset by 10 to show them the 2nd page. Offset by 20 to show them the page after that. The the client says, here's the last row and then the server says, great. Here's the next row.

And so what you lose in cursor based pagination is you don't get addressable pages because everything is around that cursor. They have to say here's the last record that I saw. But what you gain is the records can't really shift out from under you. Because if new records are added or deleted, we're still gonna show you the very next record after the one that you saw. Let me show you how this works.

So in this case, let's say, let's say that the last record that they see is Aliyah Bashirian. Okay? So then what we're gonna do for page 2, so we send out, we send out Aliyah Bashirian, ID number whatever. And let's go ahead and order by ID as well because we do have some duplicate names in here. And so we need our we need our ordering to be deterministic and so adding on that unique ID at the end will make it deterministic.

So Aliyah Bashirian, 322-714 is the last one that this user has seen. Now what we can do is when the user says, hey, give me page 2. Give me the second page. Here is the last record on the page that I saw. So we can say where first name where first name, last name, and ID and we're gonna make that row value is greater than.

So show me the next record that is beyond, first name is Aaliyah, last name is Bashirian, and the ID is this one. So in theory, this should show us the next 10 users. Let's go ahead and see what the next user is supposed to be just to see if we got it right. So Aliyah Bausch is going to be the 11th user. So in theory, Aliyah Bausch should be the first one that we see up here.

So if we run this, we see Aliyah Bausch. Yes. We did it. So what we did what we did here is the user provided us or the client application, or whomever, provided us with the, importantly, the columns by which we are ordering. That is the most important thing is we have to provide the columns by which they are ordering.

So we said, alright. I saw Aliyah Bashirian, 322714. Give me the next row after that. Order by the same ordering, limit 10, and there we get our second page of records. If you didn't do it this way, there would be a lot of, first name is greater than Aaliyah or equal to Aaliyah and last name is greater than Bashirian or first name is equal to Aaliyah, last name is equal to Bashiria and ID is greater than 322-714.

So it would get extremely messy if we didn't have this row value syntax. There's another great place where row value syntax comes in handy and that's when you're storing your dates as separate parts, as a year, month, and day column which I don't super recommend. But hey, this is a good example because we're gonna look at, at, a recursive CTE, a regular CTE, and then row value syntax. So I don't have a table here with all the dates but that's fine because I have recursive CTE's. So we're gonna do all dates as, select date, and then we'll just start with 20240101, and then we will call this as date.

And then remember, we want to union all. Select date of date plus one day from the very table that we're building where date is less than 20 24, 12/31. So this should give us this should give us the table of all dates. So if we did select star from all dates, I think that is going to work all the way up to 12:31. So that worked well.

Is this year a leap year? Oh, yeah. This year is a leap year. So that worked well. But now what I wanna do is I need to separate this out to make this example work.

I need to separate this out into year, month and day columns. So we can do that with another CTE. We'll just call this one dates as and then you can use the strftime to get the year out. So we will select, this is not recursive because we're not gonna do a loop. So we're just kind of using this to organize.

So we'll select that as year and then we'll select this one as month which I believe is a lowercase m and then we'll select I believe a lowercase d as a day. I'm still selecting from so we want dates. No such column date from, importantly. There we go. So now we have 20240101.

Those are separated out into different columns, but you can tell the indicator is that leading 0. So because that leading 0 is there, that tells me these are all still strings which makes sense. So I'm just gonna I'm just going to type juggle these into integers and we should be good to go now. So now we have all of these as, integer affinity. And what we can do now is select star from dates where year, month, day is forget that.

Let's do, where year, month, day, between and then we can say 2024 011. And let's just do the 1st week of the year. So through 7, there you go. So that is a great usage of row value because, again, to do this separately would be would be a little bit difficult. I mean, this one would be easy.

But if you were saying, like, hey. Let's do it through 2. Yeah. 27. That would get a lot harder because then you'd have to account for, well, you know, it's gotta be less than 7, but only if the month is 2.

But if the month is 1, it can be greater than 7. It can go all the way up to 31. So this is a great use case for row value syntax. What a fun example. What a fun example.

We got to do recursive CTEs, regular CTE, some date formatting, and row value syntax. So I don't recommend storing your year month and date separately, but if you have a need for it and it makes business, sense for your use case, do it. That's great. Nobody's stopping you. And you can use row value syntax for comparing those dates as a single unit rather than writing the complicated logic that would include all 3 columns.