Correlated Subqueries vs Derived Tables

One option for improving query performance is to rewrite the query in order to obtain a completely different execution plan. In this episode we look at how it's possible to rewrite queries with correlated subqueries into derived table syntax and examine what causes the difference in performance.
Subscribe and turn on notifications to never miss a weekly video: czcams.com/users/DataWithBer...
Related blog post with example code:
bertwagner.com/2019/04/23/cor...
Follow me on Twitter:
/ bertwagner

[@h2oautospa998]

Thank you for such a well done video. I subscribed because I just finished my first mySQL Database Management class. I have to re-do the class and I think your videos will be a big help.

[@robertkorth605]

I have knowed already it but brilliant presented. Thx

[@brendafosmire6519]

Super helpful videos.

[@stefanotorelli3688]

Depending on the original query window function could the trick too... but .. as always... it depends...

[@coding3438]

Hey Bert thanks for the amazing tip. I would really appreciate if you can answer this, is there any problem that absolutely requires correlated sub queries that cannot be solved by joins or derived tables.? I don’t like using them because they confuse me. Thanks.

[@ru2979]

True , They are nasty 🥺

[@ulysses1904]

Good stuff as always. BTW I have never seen the syntax like (FirstBadgeDate = ) in a select statement. Can you take a moment to explain that, or give it a name so I can research it? I have seen something like that where it was referred to as a quota query but it was in the WHERE clause, like WHERE 2 = (select COUNT(*) from employee EE where E.employeeID = EE.employeeID). I think it was Tony Andrews doing a talk on optimizing DB2 queries where he called it a quota query.

[@ulysses1904]

@@DataWithBert Thanks Bert. Looks like I was overthinking it and I didn't recognize it as a column alias.

[@zanonilabuschagne7628]

Thanks Bert. I tried your and got different results. I do want to throw another query out, and get your take on it... In the past, I have achieved great performance gains with CROSS APPLY statements with a TOP in it, but when I tested it in this scenario, the results were atrocious. CPU time and reads went through the roof...
The query is
select DISTINCT
o.UserID
,S.FirstBadgeDate
from Badges o
CROSS APPLY
(
select top 1 [Date] as FirstQuoteDate
from Badges b
WHERE b.UserID = i.UserID
Order by b.Date desc
) S
Any idea why it went so wrong?

[@MiningForPies]

You’re also selecting the last date, not the first 🤔

[@KirillBezzubkine]

nice vid. useful. thx

[@josephanglada4785]

The Final Solution