Correlated Subqueries vs Derived Tables

[@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.

[@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 🥺

[@robertkorth605]

I have knowed already it but brilliant presented. Thx

[@stefanotorelli3688]

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

[@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 🤔

[@brendafosmire6519]

Super helpful videos.

[@josephanglada4785]

The Final Solution

[@KirillBezzubkine]

nice vid. useful. thx