SQL Server Queries Part 12 - Correlated Subqueries

[@rajsingh0621]

Andrew Gould is seriously one of the best instructors I've found on youtube. You start from scratch, with simple examples and you build on your lesson plans beautifully after that. I cannot thank you enough on how amazing SQL lessons were, as well as your VBA lessons. I thought I had enough knowledge I needed for basic VBA, but still ended up learning a tremendous amount from those videos and then finally SQL. I'll admit I got confused with correlated subqueries, went on to google for a bit, but finally watched this few more times and they finally clicked. Online training courses can be expensive and so I want to thank you for providing SQL and VBA on youtube. It's greatly appreciated. There are a lot of smart people in the world, but not everyone can be a great teacher and you certainly are a great instructor and British accent definitely a plus :)

[@QuietLumina]

You’re one of my 2 go-to teachers when I need SQL help. Thanks so much for your work.

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@laitai_]

What I like most about your tutorials is how clearly you speak. I'm definitely a fan of your channel. Good job and thanks.

[@aguafria9565]

No idea how hard it was to find an English speaker amongst all the Indian videos. Your video deserves more likes

[@kamran_desu]

Best explanation of how correlated subqueries actually get processed, makes so much sense now. Thanks a lot!

[@rogeliosalvador7857]

Instablaster

[@JoeG2324]

VERY NICE EXPLANATION. correlated subqueries are also great to use in the select clause when you need to do a calculation on data. they're also great when working with the cross apply operator or exists not exists. Great explanation of this function.

[@charlyclerfeuille4212]

Reaching the last part thinking -to my very surprise- "Wait, it is the end already ? But I need more !! I must try these tools." I remember this kind feeling from a long long time ago, when discovering mathematics/logical problems as a kid with some great teachers, with this particular spark in their eyes/voice. So now, I going to crunch those exercises from the website because it may seem simple from those 2 hours explanations, but I know that there are the actual challenges / fun. Thanks a lot Mr Gould / Wise Owl ! P. S : What to joy to feel that my VBA years were not totally in vain.

[@WiseOwlTutorials]

Thanks so much for watching and for your comments! There is much more to learn about SQL and I still intend to make a refreshed version of this tutorial to flesh out some of the things that we covered only superficially. Happy to hear that you enjoyed it anyway and good luck with the exercises!

[@Hevletica]

Had to watch it twice. Your entire series is fantastic by all means.

[@WiseOwlTutorials]

Happy to hear that you're enjoying the videos, thanks for watching!

[@eRockDrums]

Thanks Andrew for the SQL Server Queries videos. Your teaching made it easy for me to understand how SQL queries are created. I appreciate the time you put into making these videos.

[@hddomundo]

I'm from Brazil and I just finished your SQL Queries course with this video. Man, Thank you a lot for all your videos, just help me immensily! But I don't stop here... lol... I grab your text content from your site and put it in my evernote for study more and make a consult when became necessary... Thanks Man!!!

[@TopeOlutola]

Awesome work Wise Owl.!!! Simple, Clear, Direct and it just penetrates the brain very easily.

[@tommyagnew]

Outstanding tutorial! Exactly what I needed to solve a problem I was working on, as I didn't understand how to make the inner subquery dependent on the outer main query. Concise and well spoken, I will check out your other tuts!

[@jackwood9716]

Using for revision, I have recommended the playlist to my year at uni. Keep up the good work!

[@mirragemelkyr]

Woah, I'm really happy of finding this channel you're clear and methodic with the basic explanations thanks a lot

[@supa.scoopa]

Thank you so much! Very clearly explained! I'll continue watching your videos!

[@WiseOwlTutorials]

Happy to hear you found it useful, thanks for watching!

[@JoshuaMcClain_JKM]

Hey thanks for this - I was getting wrapped around the axle on Correlated Queries, this help break thru that mental block.

[@hadireg]

nothing is left random, all precisely explained!! Great Teaching skill! Cheers!

[@WiseOwlTutorials]

Thank you!

[@PremKumar-wy3mx]

Really your videos helped me many times to solve real time problems in my project! Thanks a lot ! :)

[@m.a.saberi7606]

thank WiseOwl for this piece of artwork. I like your way of teaching.

[@LookLessSeeMore]

Agree with Rajpinderjit - very well explained SQL query - very logical - Thankyou

[@krismaly]

Good video for Sub Query and Correlated Sub Query. Thanks a bunch for educating world audiences.

[@Victor-Momo]

Thank you for the course. Extremely enjoyed it.

[@justinbanza4751]

Thank you very much for this video and all the Serie about sql Queries, it is very helpfull

[@WiseOwlTutorials]

My pleasure Justin! Happy to hear that you found the videos useful and thank you for watching!

[@HarshathKumarBayyaram]

That's a great tutorial short and very informative Thank you so much

[@WiseOwlTutorials]

You're very welcome, thank you for watching!

[@ArunSharma1978]

Thanks the explanation was detail covering from syntax to logical execution.

[@eddiekumar9318]

Excellent video, this reminded my old notes. Thanks.

[@youngcolt5305]

Correlated Subqueries is a relatively tougher topic for beginners. You should add some more examples or small exercises in this video for practice.

[@sudhakarr1404]

Hi Andrew, all these tutorials are very clear, up to the point and at no point a single minute was wasted, i think. I have gone through all 12 tutorials, but wondering are there any more available on the top of 12 ??

[@sudhakarr1404]

Hi Andrew, Thank you for the fast response to my query. Will go through them definitely. Preparing to sit for Microsoft exam, so are there any exercises from your side to practice for exam? Thank you in advance.

[@sudhakarr1404]

Andrew, as you are an expert in this area, would you mind in recommending any reference sites to gather exercise material. Thank you.

[@sudhakarr1404]

Thanks Andrew!

[@amitkooverjee6596]

Thanks for the videos. Really useful :)

[@sristeska1994]

Great job :) You really helped me with understanding the correlated subqueries :D Thanks!!!

[@hameddadgour]

Very clear explanation :)

[@grimskull416]

Not that it really mattered in this tutorial, but I think that for the "FilmRunTimeMinutes" in the AVG(FilmRunTimeMinutes) statement (8:00), it's missing a table name (f or g).

[@tlcd8374]

This is very helpful. Thanks a lot for this.....

[@krzysztofmaciak1611]

Are you going to make a video about window function (OVER clause) ? It can sometimes replace correlated subqueries.

[@AnilKumarP4U]

Hello WiseOwlTutorials, Thanks very much by sharing these tutorial. Could you please make a video on pivot and unpivot, that helps us a lot. Thanks, Anilkumar

[@JosueLopezXIX]

Amazing Video! Thank you!

[@nathanielmusitano7804]

Great videos man, love the how clearly you explain things. I was wondering if you have made any videos on SSIS or SSAS, and if you did where can I find them?

[@FountainRaider]

You're awesome

[@jk6492]

Hi, WOT, query around 5:52 is not working, I can't see any result. and when I try only subquery, "The multi-part identifier "F.FilmCountryID" could not be bound." this error came out. ------------------------- SELECT c.CountryName ,f.FilmName ,f.FilmRunTimeMinutes FROM tblFilm As f INNER JOIN tblCountry AS c ON c.CountryID=f.FilmCountryID WHERE f.FilmCountryID= ( SELECT MAX(FilmRunTimeMinutes) FROM tblFilm as g WHERE f.FilmCountryID=g.FilmCountryID ) ------------------- Could you give me a hint?

[@atalbrecht]

Not sure about the error, but in the video, he has "WHERE f.FilmRunTimeMinutes = (" instead of "WHERE f.FilmCountryID = (", so I'd suggest starting there.

[@LuigiZambetti]

Hello, could you please add one or more video also for the Pivot operator and about working with XML datatype?

[@fernandoloureiro9681]

u helped me a lot! Thx

[@Lexartm]

What I didn't understand is what the f. and g. prefixes are. Don't you usually have to put the table name before the attributes?

[@nik0096]

I skipped the part which has "where YEAR(g.FilmReleaseDate) = YEAR(f.FilmReleaseDate ) " for the last query mentioned in the video and the output I obtained was "Around the World in 80 days" as the first row because the FilmRuntimeMinutes is 167 and the average is 126. Its a film from 1956. But I didn't see that in the output described in the video. Please let me know if I misunderstood something. Thanks.

[@mariei7445]

If u skip the where clause in the correlated subquery, it no longer is one, so u should expect to get the same answer. I think u need to rephrase your question.

[@jasonreyes9290]

Great! Thank You!

[@2152-p4c]

I don't understand the inner subquery logic on g.FilmCountryID = f.FIlmCountryID. Can someone explain the significance of why you're equating the same column from the same identical table via a different alias?

[@uzferry5524]

21 52 you take one value from outer query and then do something with all the films that contain that certain value as well in the inner query. think of it as two different for loops, if you're familiar with a programming language like C

[@uzferry5524]

21 52 what I dont like about sql that this isnt very efficient, you could do the same in one loop in C - not sure if you could do it more efficiently with sql, however. i can be mistaken of course since im not familiar with sql

[@wexwexexort]

I also find it kind of complex but I'll try to write what I understand. Let's go step by step. Select max(RunTimeMinutes) from Film >>>(will return) the highest value in RunTimeMinutes Field From Film Table Select title, runtimeminutes from film where runtimeminutes = (Select MAX(RunTimeMinutes) from Film) >>> Will return the title and runtimeminutes values Where Subquery(true) Let's try this; Select title, runtimeminutes from film where runtimeminutes = (Select MAX(RunTimeMinutes) from Film Where Film.CountryID = 241) >>>Will do the same thing but additional constraint in subquery, not from whole Film table but Where Film.CountryID = 241. So what if I want to get it for each Country ? SELECT title, runtimeminutes, film.CountryID from film WHERE RunTimeMinutes = (SELECT MAX(RunTimeMinutes) FROM Film AS G WHERE Film.CountryID = G.CountryID) >>> It's like inner join in subquery. Still it's meaningless to get CountryID in our output table. What's the meaning of countryID 241 or 260? So, we INNER JOIN with Country Table to get Country Names. SELECT Film.Title, Country.Country, Film.RunTimeMinutes From Film INNER JOIN Country ON Country.CountryID = Film.CountryID WHERE RunTimeMinutes = (SELECT MAX(RunTimeMinutes) From Film AS G Where Film.CountryID = G.CountryID) You can try it on sql to understand much better. I hope I wont confuse anyone. BTW, You are awesome WiseOwl. Thank you.

[@Subhash_VCU]

Could you explain when countryid=241 , hits the runtime =195 , i.e how would the main query figures and executes the o/p display as country name :unitedstates , filmname : Schindlers list and Filmruntime = 195

[@MsDKAV]

What if I needed to get the max RunTimeMinutes for the longest film played in each cinema of each city in each country?? and all the information is also in the same table. Not really sure what to do. help?

[@MsDKAV]

Thank you Sherlock ;) that helped

[@rdg8268]

Is it similar to the group by clause?

[@YasirMustafaIsper]

nice, you help me . thanks

[@kiyasuihito]

Yay! No indian accent!

[@snackymcgoo1539]

You should be ashamed of yourself for teaching correlated subqueries. Inefficient and impossible to trouble shoot the return results without converting the subquery into a temp table.