Subqueries

[00:00:00]
>> Brian Holt: So what if people want to find all customer invoices using a certain email address? So we haven't looked at invoices yet, I don't think, it's invoice is the name of it.
>> Brian Holt: You can see this is a invoice table. There's an ID, Customer ID, it's a foreign key that refers something to the customer table, date, address, all that kind of stuff.

[00:00:28]
And we wanna know all invoices that come from a certain email. That email comes from customer. So schema customer. You can see email is right there, right? So how do we reference that? So how do we filter that? Right, we have the invoice table. We wanna get all the invoices that belong to a certain customer.

[00:01:01]
I've already given the tools to do it one way, right? Which is joins, right? We can just join the tables and filter on there. That's exactly what this does.
>> Brian Holt: That's a little small.
>> Brian Holt: Maybe we just want to just do invoice ID for now, i.invoiceid and c.email.

[00:01:36]
>> Brian Holt: Right, so you can see these are all of the invoices that we have that are from hholy@gmail.com. Pretty cool, we could definitely be done there. There's properly zero things wrong with doing it this way. So if you're happy with that, then just skip this part of the course.

[00:01:55]
Now I'm just kidding. You do wanna learn subqueries, but I'm very happy with this query. However, we're looking for just one email, right? We actually don't need everything in the customer table. We just need one, right? And sometimes it can kind of be a pain in the butt to think through all of the join, right?

[00:02:16]
Okay, I have this table, this ID, this table refers to this ID over here, just, join them on this. Also that results that can be enormous, right? And then it's kind of big to filter all of that down, to join everything together, just to filter it back down, especially if it's not indexed.

[00:02:36]
Let's just go different. It's gonna have a different performance profile. But the jury's out if it's better, right? But in certain cases it would be better. Certain cases, it's probably worse as well. So we're gonna, select invoice ID from invoice. Where customer ID equals, opening parentheses, and then we're gonna just write a second query inside of here.

[00:03:11]
Select customer ID from customer where email equals whatever email we're looking for, hholy@gmail.com close parentheses. There you go. So this should work as well, right? We got all of the things back here.
>> Brian Holt: So what is this? This is a subquery, it's going to run this query first.

[00:03:46]
Whatever gets selected from this CustomerId is then going to be given to this.
>> Brian Holt: Sometimes it might be better, sometimes it might be worse. In this case, it doesn't matter, because our results just really not that big, right? So either one of these is totally, totally fine. I could see this in scenarios where this is an indexed query, where it's really, really fast.

[00:04:12]
And then we're only getting a couple things out of this table, and maybe that that part's inefficient, I would see this being a superior way of doing that, right? Maybe you need to reference a bunch of stuff from both tables, in which case, yeah, you'd probably need to join things.

[00:04:25]
It gets really weird and complicated when you're trying to do multiple kind of things together with sub queries.
>> Brian Holt: But just know there are a tool out there sometimes two queries is better than one. Sometimes they're not. Actually, I don't put this under my course, but there's actually a kind of idea in SQLite that running lots of little fast queries is better than running one really big slow query.

[00:04:53]
And that's a bit counterintuitive especially for old school database developers. Generally, the thought was always one query, one result set, don't run multiple queries. And the reason for that is that you have a server here, and you have your client over here, and if you're sending lots of stuff over the wire, it's always network latency that's the slowest part of your stack, right?

[00:05:13]
Because you can't go faster than the speed of light. With SQLite it's not a problem, cuz you're just on the same computer, right? You're going at the speed of electrons, right? Just going across your CPU. So there's a bit of a paradigm shift there with thinking about SQLite running locally that lots of little queries is good.

[00:05:36]
And they state that in their docs.
>> Brian Holt: So in which case, this might be faster. It's always gonna be a big, it depends, right? And the other thing I'm always gonna tell you is, don't try and guess, right? Always try and wait for it to become a problem before you try and solve the problem.

[00:05:56]
Premature optimization, you're always gonna solve the wrong problem.