Selecting, Paginating, & Using Where Clauses

[00:00:00]
>> Let's talk about select. I think I decided to go kinda out of order. Most people learn Select first and then they learn how these other things that I've been teaching you. But I wanted to show you how to construct data and model data before we started looking at data, right?

[00:00:17]
So now we're actually gonna start looking at the data. The most classic of them all is SELECT star FROM whatever table you're looking at. And this is going to give you literally everything back in the table. If you had 10,000 columns in your table this will give you back 10,000 rows.

[00:00:33]
You can see my computer can't even show them all at once. But that is what this did, right? And so you can see everything that we've inserted in here. You can see it does skip some numbers here and there because that's step that we removed or deleted, or something like that.

[00:00:50]
But here we are, and we have a bunch of various different ingredients. So, SELECT you're telling it you're gonna pull things out of the database. SELECT does not update anything, all it does is pull data out, it just reads, right? Star is everything, give me all of the columns, FROM, you're asking from what table you want it to come from.

[00:01:15]
You're telling it you want it from in the ingredients table. So that is it. What if I only need to see the title and the type? So notice there's no ID here and there's no image. I'm going to recommend that in general for your code, right? And talking about for your JavaScript or Python or Ruby applications, you should specify what you want.

[00:01:50]
And don't just do star, star is lazy. And it makes it more intentional that later when you come back to read your code that you know I was asking for these specific things. You're being very intentional and kind of self-documenting in your code that you're asking for specific things, rather than just give me everything.

[00:02:08]
And the side benefit is slightly smaller, payloads going back and forth between your application and your database, it's a little less processing of what's going in and out. Not really the point, it's so menial and small for the most part that you don't really care. The bigger advantage is that you're being more intentional with your code.

[00:02:31]
So if my JavaScript application just needs a list of types and titles, but does not need the images, don't do the star, just to select title and type from ingredients. I'm asking that as your potential future coworker to help me out there. What if I only want ten things instead of everything?

[00:02:50]
Which is generally the case as well. If you have, I mean, can you imagine trying to do select star from Facebook's user table or something like that? It would crash all the servers, right? Literally billions of rows. So, add yourself a little limit there and that'll give you back however many things that you asked for.

[00:03:13]
So I asked for 10 things, so this will give me back only ten things, right? You can see there are 10 rows. It's a little bit better than a billion rows, right? So, whenever you're doing selects, unless you really know that there's not gonna be very many things or unless you're intentionally trying to get everything out of a database, you're basically always going to have a limit.

[00:03:37]
So I have the first ten things, what if I want the next ten things? Offset 10. So that now gives me the next 10 things. And if I want the next things, offset, 20, and so on and so forth. Eventually, it'll get to the last one, you can see we only have one in this.

[00:03:58]
So even though I said limit 10, it's like I don't have 10 to give you, right? So you just get 1. Now, what is the problem with this? Let's say, this is called pagination, right? I'm paging through my data. So a user queries my page like this, right?

[00:04:15]
And they're seeing all of these ingredients, and then let's say we have a problem and we decided to delete black pepper. Right, so delete from database black pepper. They then ask for the second thing. All of these rows have now shifted up. So what happens? Pineapple is actually gonna come into here, right?

[00:04:43]
Because there's one less row here cuz I asked for, and then the offset is precluding that, right? So basically, the user is never ever gonna see pineapple. It's kinda weird, right? Now, if we're talking about ingredients, it's probably not the end of the world that they skipped an ingredient, right?

[00:05:03]
Actually, it might be, but I would imagine most cases it's probably okay. So this is the dangers of offset, and there's kinda duo danger as well. But first of all is that if you're paging through it, you might miss things because things are being inserted and deleted and things like that.

[00:05:21]
And actually a better way of doing this sort of pagination, Is to use these IDs, right? So rather than say offset 20. I can actually just give it a WHERE id is greater than 32. Or let's do it after 23. Right, now if I delete something or something like that, we're anchoring on a row in the table rather than anchoring on some sort of offset.

[00:06:03]
So, if you're paging through data in your particular service, this is a better way, it's also way more performant. Cuz think about it, if you ask for give me 100 rows and give me an offset of a 100, your database has to go organize it, it has to count a 1000, and then it gives you the next 100, right?

[00:06:24]
If you keep doing that over and over again, it gets kinda expensive. Whereas, if you're just saying, hey, give me everything bigger than this ID, it can just go right to that ID, and then give you everything after that. That ends up being a lot more performing. So it's an optimization, you don't always have to do it.

[00:06:40]
For our application that we do today, feel free to use offset. I just want you to know that that's a way better way of paging through data, yeah.
>> So with PostgreSQL databases, people tend to use just numeric ids versus Mongo. It's this big, like cash.
>> Yeah, it's a good question.

[00:07:01]
SQL database is almost and always in general, they're just numbers. And these numbers might get to be 100 million or a billion or something like that. I imagine you're checking account ID with your bank, it's probably their primary key in their Oracle database, right? That's probably where they get that, right?

[00:07:22]
Whereas something like Mongo or some other databases, they give you objects. It's not really a concept here of that sort of thing in SQL, it's gonna be a number.
>> It's more like a UU ID or something like that.
>> Yeah, exactly. There's probably other ways of doing it, but it's almost always just an integer identifier.

[00:07:42]
There are instances where you don't need an integer primary key. And I will show you that once we get into joins. Basically you'll end up having some sort of other things be a primary key. And then you will not have a use for incrementing ID, but I can't think of a reason why I would like to use a hash or something like that.

[00:08:05]
That wouldn't make any sense to me, the ID would make a lot more sense. And the reason why I say that is you wanna join things between tables and things like that. It's way easier to deal with integers just mentally. And SQL is built for it, so there's no reason not to.

[00:08:20]
Maybe it's stated a little bit differently from a Mongo perspective. Mongo you really never wanna do joins. This is not a Mongo course so we're not gonna get too much into it. But you don't have two collections and then you try and join them together. If you're doing that you're doing Mongo wrong for the most part, it's possible, it's expensive.

[00:08:40]
SQL, you're gonna do joins everywhere because SQL, it's a relational database. There is RDBMS, relational database management system. RDBMS is what people call disk style databases. It's literally built into the name that these are supposed to be relational, which means those keys are supposed to line up. Which means it makes sense for those to be integer numbers.

[00:09:05]
We did limit, we did offset and I kind of hinted at the next thing that we're gonna talk about which is where. So we can give a clauses where like I only wanna select specific kinds of things. So if I say SELECT id, title, well, that was mangled title, type, FROM ingredients WHERE, and I can say where type = fruit.

[00:09:37]
I put an extra comma there, don't put the extra comma. This WHERE is just basically is like, hey, make sure that this where clause holds true, right? We're giving it some sort of conditional to filter up on. There's a bunch of stuff you can do, I showed you.

[00:09:55]
You can do WHERE, you can do numbers, right, where number's greater than, you can put greater than or equal. We can also say, let's say we wanna select everything that's not a fruit. This little symbol right there where it's just angle brackets put together means not equal. So now I'm getting everything that's not a fruit, vegetables, meats, others, those kinds of things.

[00:10:16]
I can do things like, SELECT * FROM ingredients WHERE id is less than or equal to10. You can also put AND, right, or OR. We'll do AND, right now ID is greater than 20 and then LIMIT 10, right? So this is gonna give you everything, that's not what I wanted to do rather, I wanted to do this.

[00:10:54]
Or that, I did that the wrong way, I did it the wrong way, didn't I? From ingredients WHERE id is greater than or equal to 10 or less than or equal to not like that, There we go. So this will now limit everything that I get between these numbers, right?

[00:11:21]
So that's what these ends, both these have to hold true. You can also put OR in here. Right, so now this has given me anything that's outside of those limits. Anyway, good, I had these all right in here. I don't know about you, but like I always have to think about the alligator and which one's greater than or equal to.

[00:11:48]
And I have to like check five times, but I got it right here There we go. So here I'm asking for where the id is outside of the range of 10 to 20, right? Inclusive of those, right? So, you can see this goes, 1, 2, 3, 4, 5, 6, 7, 8, 10, and then it skips everything 10 to 20 because that's what this or does here.

[00:12:21]
So there's a bunch of other conditions you can do that. We're gonna look at fuzzy text search and some other things like that here in a little bit. But just know that it's not just equals or less than or equals that, there are others as well. Last one I wanted to show you here is SELECT * FROM ingredients, WHERE, Let's say LIMIT 10 here for just a second.

[00:12:49]
By default, these are generally going to be ordered by ID cuz that's gonna be generally the order that they were inserted into. But just know according to the SQs pack, it's not required you to give back anything in any order, right? Generally it's going to give it back to you in the order of insertion.

[00:13:06]
But that's actually not required and you probably shouldn't rely on it in your application. If you care about order, you need to provide a order by. So in this case, ORDER BY id would get you the, Same thing, but let's say you wanted it in descending order. So now it's 33,32, 31, right?

[00:13:34]
It's giving you back in reverse order. That's what that DESC means, descending, right? Just so you know, you can put ASC in there, ascending. But that's actually just the default, it's just optional if that's important to you to be specific. But you can also put ORDER BY title in there, right?

[00:13:56]
So now it's gonna give it back to you in alphabetical order. Likewise, ORDER BY title DESCENDING, it'll give you back watermelon, tomato, sugar and strawberry in that order as well. In other words, if you care about order, you should provide order because while Postgres is generally consistent, it doesn't have to be.

[00:14:22]
Okay, questions about ORDER BY? Yeah, Dustin.
>> Do you have any preference between the CLI or some kind of SQL client when you're working with an SQL database?
>> It's good question. Do I prefer using the command line or do I use something like, I mean, the other option would be PGAdmin, which we will see in the second half of the course?

[00:14:48]
I almost always use PSQL because I just always have. That's what I learned on, that's what I continue doing. It's not a good reason, but it's just my reason. There's other people that grew up using things like PGAdmin or PHPMyAdmin for MySQL, also very common. And I think JetBrains has a really good SQL client as well, I can't remember what's it called.

[00:15:18]
There's VS code or VS extensions for querying databases that are visual. I like writing queries and seeing the results, so this is what I do. But sometimes it's really useful to take that data and make graphs out of it and stuff like that. And things like a client, some sort of Gooey can help with that.

[00:15:40]
Obviously, this doesn't help you with graphs, right? So, you can also generate CSVs and put that in excel, and then excel can generate graphs for you. I've done that a bunch, but I'm just used to doing it this way.