Using Subqueries

[00:00:00]
>> Let's talk about subqueries. I was actually pretty impressed that I got this far in the course and I did not actually end up talking about subqueries until now, cuz I end up using them quite a bit. What if you're looking for the cast of Tron Legacy, but you don't remember the ID?

[00:00:22]
There's a couple ways you could do it. I'm sure you could probably fathom it with different things, you could do joins and things like that or we can just do a subquery. So you can do SELECT, p.name for person.name, FROM casts c, INNER JOIN people p ON c.person_id = p.id.

[00:01:01]
WHERE, and you just wanna say like c.movie_id = whatever the id is of Tron Legacy, right? Well, you can do that, actually you can say, =, and then you're gonna put a parenthesis here. And can actually put a second query in here. So I'm gonna say SELECT id from movies, where name = 'Tron Legacy'.

[00:01:31]
And then I'm just gonna close that particular subquery, that's what the parentheses mean, and I just hit semicolon. So I just wrote my query down to the point where I need to get a second query here to figure out whatever the id is from what Tron Legacy is.

[00:01:50]
Because this is only going to return one id and I know that, I can just go ahead and do this. And SQL is smart enough to say, cool, we're gonna grab this from this subquery, return that back up here, so this one gets run first. And then the result of that is gonna be put here to run the second query.

[00:02:11]
Very common, you're gonna use subqueries all the time. But keep in mind that you've turned now one query into two queries, there's a performance implication for that. Nesting makes things harder to read. Frequently, you can do this in a better way. I would typically air on what's the most readable query versus what's the most performant one.

[00:02:35]
Typically a performance profile's not gonna be that different. If it is that different then you need to make adjustments. But if they're the same, then error on the side of what's readable. Sometimes subqueries make it better, sometimes subqueries don't make it better, and you have to be the judge of that.

[00:02:49]
So just to drive home a point, this is this query with a subquery. This is the exact same query using joins. Which is clear to you? I have an opinion, but it may not be the right opinion according to you. I do think this one in this case, this is more clear.

[00:03:19]
Because this is a pretty simple query with a very simple where, and this where the SELECT query here is very simple. It's just getting an ID out of the movies table. Two INNER JOINs with an AND. There's some gymnastics for my brain to get there.
>> And it kinda feels like if you did the wrong join at the wrong time on a performance database you could-

[00:03:46]
>> It can go from fine to out of control
>> Out of control really fast. I feel it'd be harder to mess it up have you ever noticed that subquery,. You know exactly what you're looking for, I don't know?
>> I mean, this can kinda suck particularly if there's no index on name.

[00:04:01]
>> There.
>> So it's really gonna depend, in my particular case here, the subquery one was 308 and I think this is without the index, and the second one was 175 Depending on what you're doing, that's might be acceptable, that might not be acceptable, it just really depends.

[00:04:25]
If it's a Cron job that runs once a week, who cares, right? It could be 10,000 I don't care, right? If this gets run 100,000 times a second, I very much care. Your joins are typically gonna be faster, just as a general rule, joins are gonna be faster than subqueries unless you have some, Interesting circumstances that would dictate otherwise.

[00:04:46]
But I'm just gonna say on the whole, you can assume that subqueries are gonna be slower.