Understanding Correlated Queries in SQL

[@vipu1212]

These 8 minutes solved hours' of my headache. Very well explained

[@charan_75]

For me, days of headache.

[@N7__7]

😂

[@TheDataMaestro]

What makes this video awesome is: clear explanation of SQL feature, best practices of SQL, and alternative solutions. Those 3 are missing from many online courses.

[@clovvnnor7113]

I was struggling with the logic of "WHERE dep = E. dep" part for almost a week, and ur small example is just so crispy and clear, I LOVE u man

[@Sunny-wq9ue]

I still don't quite get that part? Could you explain please?

[@adityabhatt4173]

Sharing the Code for the same- /* Understainding a correlated subquery and a common use case To find out employees who have salaries greater then the avg sal of their dept */ DROP TABLE IF EXISTS COLLEGE; CREATE TABLE COLLEGE ( ROLL_ID INT , NAME VARCHAR(20), SALARY INT, DEPT VARCHAR(20) ) INSERT INTO COLLEGE VALUES(1,'Aditya',2000,'CSE'); INSERT INTO COLLEGE VALUES(2,'Aman',3000,'EC'); INSERT INTO COLLEGE VALUES(3,'Arun',3000,'CSE'); INSERT INTO COLLEGE VALUES(4,'Amit',4000,'EC'); SELECT roll_id, NAME FROM COLLEGE O WHERE SALARY > (SELECT AVG(SALARY) FROM COLLEGE WHERE DEPT=O.DEPT GROUP BY DEPT);

[@telephonerock]

Best explanation for correlated queries on KZbin.

[@oqant0424]

Great, thank you! Much easier to understand for those of us that need to visualize how it works. Spent the last 24 hours trying to understand this. Sincere thanks to you for this great explanation.

[@akhdanarif9416]

8 minutes of cure to people's headache all over the world. Best potion ever.

[@pablo_brianese]

The most important part of the video is, in my opinion, the comparison with views and joins. It shows us why we would want to use correlated subqueries.

[@kareemadesola6522]

Could you tell me the graphics tool you used for this tutorial? Would really much appreciate it

[@prinonmahdi1047]

Spent the last 24 hours trying to understand this. Sincere thanks to you for this great explanation.

[@chaos8514]

how does it group does it group each time outer query runs?

[@daniellestellrecht4105]

Great, thank you! Much easier to understand than the Microsoft online course, for those of us that need to visualize how it works.

[@dssingh9955]

Hey, can you help me in some complex sub query??

[@rajsekhardutta8891]

Finally found a video that explains the topic in clear concise manner. Great explanation. Thanks!

[@MySqueezingArm]

Hey man, thank you for explaining something that I wasn't wrapping my head around. I thought it was a weird loop, but I realize it's just doing multiple comparisons (duh). Thank you so much.

[@ranjithparuchuri6463]

shall we write a query as select * from emp where sal>( select avg(sal) from emp group by deptno) with out using corelated sub query ??

[@diegomartins5846]

I would have solve it like that 👍

[@Manoj_M_S]

Better than so many websites and videos explaining corelated query.. Nandri

[@rishi4]

Thanks a ton I was having so much trouble wrapping my head around this, u really helped me out, but I have a question, it looks like this is modeled similar to a Join, when it comes to Joins If I understand correctly there are essentially 2 ways to do it, 1) using the where clause (which causes problems for full joins) 2) using a Join clause and an ON clause (which I think is the reccomended method) is there any way to use the On clause instead of a Where in a Co-related subqueiry or does it not matter in this case?

[@muhammadsafiullah8428]

Emp as E doesn't mean that we are treating that specific RECORD/TUPLE as E. But it means that we are treating the table Emp as E.

[@sindhujasekar4018]

E is known as alias name for the table that means the temporary name for the table

[@Robay146]

Wow, thank you so much! You have a gift for teaching! 🙏🏾

[@elifgurel1262]

do you not have to group by the department first and take the avg?

[@harshpandey8559]

No, since he's essential using a where clause. After the department filter, your avg(salary) essentially only grabs average for that specific department.

[@TheDataMaestro]

@@harshpandey8559 correct, and he explains that depending on size of table, this solution could use additional system resources. Using GROUP BY is an alternate solution.

[@sknrendra]

your explanations are so easy to understand! greetings from Indonesia.

[@radosawlament6794]

let me tell you my friend, all the comments are accurate, these 8 minutes saved me hours of analysis

[@prithwishsen6293]

Aggregate function cannot be used with where clause... So how come the inner query contains both togethee

[@Cool94_4u]

bro where can be used with AGGREGATE func, the thing is you need to apply it on column which is not used on AGGREGATE func, ex. SELECT count(id) FROM test WHERE product_id10 HAVING count(id) >5

[@habzy1987]

very very helpful explanation. your content and tone of voice and pace is excellent. :)

[@kathielizabeth4790]

Good morning sir, where we will use this and only we will use in salaries...?

[@lalit-singh-bisht]

can you explain if we want to find the 3rd highest salary employee using nested query>???

[@Shreymusic05]

How can we compare single value (Sal) with multiple values? Will it not cause error?

[@urinamilovic2764]

Your explanation is great!! Thank you so much!

[@RonaldJon12]

Is the inner query executed just once or is it executed for every row returned by the outer?

[@drew1035]

Watch the video again. This is one of the first things he explains..

[@tejuspn2406]

Very well explained! Loved it! Thank you!

[@kobayashi8190]

it is like iterate over rows in outer query (calling WHERE with column argument would iterate over rows) and copy the column value to the inner query (e.eid would evaluate to current row's eid value) sound like A nested loops in disguise...

[@sagaromer8385]

why don't we use group by for average salary?

[@adilismail3593]

What if i dont use E and directly use Emp. Dep

[@danee593]

What a great explanation!

[@TheMaverickanupam]

Why can't we use group by dept in the subquery rather than referencing the outer query???????

[@vikassharma2665]

The above example is taken from Stack Overflow but I like the way you explained :)

[@sreepaljsp]

may be, stack over flow got the content from this video.. or even if it is from stack over flow, he explained it very well..

[@suryadevararama2056]

can the question that is solved in this video using corelated query be solved using JOIN?

[@SarahFeng92]

this was extremely helpful. thank you!

[@OmPrakash-ot3nr]

Can the explained question be done with the help with joins? If yes, can someone please share the query.

[@muhammadahsanbutt2491]

If u Come up with SQL Microsoft by solving it practically with Table Form It will be more efficient :)

[@hoannguyen2219]

so touching for an excellent video

[@pengxiaohan3371]

Really good, sir! Clearly understood!

[@kurakularajesh3460]

In Agregate function of avg() ,where class cant be used in aggregate function it will get error sir.

[@mallikarjun5000]

we can use

[@kalashnikov203]

Clear explanation ❤

[@muhammadzakiahmad8069]

Thankyou very well explained.

[@subhasreegupta3937]

Really good explanation

[@jullienb2055]

Thanks bro, great video keep the good stuff

[@jasminesultanah3270]

can you have subqueries within your inner query?

[@peoples_channel]

Jasmine Sultanah yes you can have...

[@user-sj9xq6hb9p]

Very well explained

[@excessreactant9045]

This made me happy, thanks :)

[@BHAWANISINGH-gi6jw]

Sir please upload video for class 11 ip mysql .it's a humble request

[@abbloos]

Thank you so much!! I didn't understand this part but now I do

[@caniceokoro7741]

Very helpful, thank you.

[@pranjalgrover5447]

Well explained! Thank you!

[@pantadeusz293]

Thank you very much!

[@SelmaPreferParis]

very helpful!

[@phantrunghoaiminh]

Great explanation! Thanks a lot! :)

[@rajdeepdey7093]

very helpful....thank you very much

[@plekkchand]

Very helpful. What language is being spoken?

[@guilhermedealmeidacosta6644]

Thank you for the video.

[@SpaceNStuff]

Helped me a lot, thnx

[@navaneesubburaj3086]

nice tutorial thank you so much

[@veenasgoodtimes2781]

thank you so much......i was so helpless.......

[@jenspeter437]

thank you my friend

[@deemaalabdulaali3147]

thank you so much

[@Martin-zm8gq]

Curry inside a curry?

[@bquimby5223]

Bro. Thank you.

[@Lumary]

Thanks a lot! :)

[@aleksandar5323]

Thank you!

[@moutazka87]

Thank you

[@suziewilliams2146]

Perfect!

[@2002S40]

IN subquery is one of worst practice to In SQL. Should've use join.

[@korneelbaeten4040]

OK!

[@aqibfayyaz1619]

Nice

[@techlovewithas445]

Thanks

[@SquireVaporwave]

thank you based SQL man

[@yugiyuva]

Too too too much thanks

[@Xakanis]

I love you

[@RamSharma-jy8mj]

very good explanation

[@atlasmazigh666]

Thank you so much

[@ishanfernando7521]

Thank you

[@PRANAVMAPPOLI]

Nice

[@ranellabjata5170]

thanks