Understanding Correlated Queries in SQL

IMPORTANT LINKS:
Both of the above mentioned platforms are COMPLETELY FREE, so feel free to Explore, Learn, Practice & Share!

Our Social Media Links:

Last but not the least, SUBSCRIBE our YouTube channel to stay updated about the regularly uploaded new videos.

[vipu]

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

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

[clovvnnor]

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

[telephonerock]

Best explanation for correlated queries on YouTube.

[oqant]

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.

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

[ranjithparuchuri]

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 ??

[adityabhatt]

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);

[kobayashi]

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

[akhdanarif]

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

[daniellestellrecht]

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

[Robay]

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

[rajsekhardutta]

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.

[prinonmahdi]

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

[kareemadesola]

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

[muhammadsafiullah]

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.

[Manoj_M_S]

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

[SarahFeng]

this was extremely helpful. thank you!

[rishi]

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?