Oftentimes in your data, you will have values that are missing which we represent as nulls. That much we know. We do need to talk about comparing null values to non null values and sorting null values mixed in with non null values. Let's start with comparing. So if you were to do select 1, that's easy.

We just get 1 back. But if you do select 1 equals null, we get null back. And what this is telling us is that we don't know if this value is true or false. It is simply unknown. It's unknown and it's unknowable because null means a null or not there.

And so when you say is one equal to null, we don't really know. It's an unknown value. It's an unknown value. So instead of saying equal, you can say one is 1 and you get true back. 1 is 0, one is null and you get an actual value back instead of an unknown.

So in other databases there's such thing as like a null safe comparison. The null safe comparison in SQLite is is and is not. So you can do is null or is not null and you get an actual true false back instead of, instead of an unknown value. When it comes to sorting with nulls, nulls are considered small in SQLite. So if we were to do this, select star from categories and we look at that, electronics has no parent ID, so that's a good one.

So we will order by parent ID and we see that the null comes first. And if we were to switch it around to descending, big number comes first, null goes at the very end. So null is smaller than every other value. However, what if you wanted the big numbers at the top, so you didn't wanna switch the whole thing around. You didn't wanna say ascending but you do want the null at the top as well.

You can do that in SQLite, which is very cool. Not all databases support this. You can say nulls first. And you can of course switch it around and say let's go back to ascending, but put the nulls last as well. This is very, very cool.

Can be really helpful but at least you know it exists. You cannot create an index like this though. So if you were to say create index, we'll just call it PID on categories, you could say parent ID ascending. You can create an ascending or descending index. So we'll drop that index drop index PID.

So you could change this to descending. That's fine. And if we were to drop it again, you cannot unfortunately say nulls last. It's invalid or unsupported use of nulls last. So, if you were to create it, parent ID descending and then descending, explain query plan, you'll see it still uses the PID index, but you weren't able to append you weren't able to append that nulls last to the actual index.

So that's just a little bit confusing a little bit confusing to me, but it does look like that index is still being used, even though we have this nulls last on there. And if we were to drop that off, you'll see it's still it's still being used. So I don't know why it doesn't support that, but the ability to control where the nulls end up in your result set is pretty cool and you can do that with nulls first or nulls last.