Subquery in SQL | Correlated Subquery + Complete SQL Subqueries Tutorial
HTML-код
- Опубликовано: 31 янв 2022
- Visit brilliant.org/techTFQ/ to get started learning STEM for free, and the first 200 people will get 20% off their annual premium subscription.
This is the SQL Subquery Tutorial video which gives you a complete understanding of subqueries in SQL. We start with explaining what a subquery is and then writing a sample SQL query containing a subquery and then explaining how SQL processes a query statement containing a subquery.
In this video we shall also look at the different types of subquery in SQL by writing SQL queries which uses these different types of subqueries. We shall look at single row subquery, multiple row sub query, single column subquery, multiple column subquery and also the correlated subqueries.
This video will also explain about the different Clauses in SQL query where it is allowed to have a subquery and we shall look at example queries to explain these differences.
Finally, we will look at the SQL commands like INSERT, UPDATE and DELETE where it is allowed to use subqueries.
Hopefully, this videos gives you a complete understanding of what subqueries are, how to use them and where to use them.
My recommended SQL Course: learnsql.com/?ref=thoufiqmoha...
[All the scripts and SQL queries used in this video is shared in my Discord server. You can join my discord by clicking on the below link. It's FREE]
Join my Discord Server: / discord
FTC disclaimer: This video was sponsored by Brilliant.
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
THANK YOU,
Thoufiq
It's impossible to have a better video than this one about subqueries, here on RUclips. I challenge someone to present me something at this level, of didactics, examples and duration. THANK YOU TEACHER! WILL FOLLOW YOU IN ALL YOUR VIDEOS!
Thank you for the amazing feedback bro.
I am so glad you found this helpful :)
I 101% agreed Bruno. I love the way he explained
@@techTFQ You are rocking bro
Awesome
You said didactics!!!!! Awesome.
And I concur!!!
Can we all take a moment to appreciate this guy, providing such amazing content for free? Hats off to you Sir. These videos are extremely useful for students like me :)
YES ,REALLY AWESOME VIDEO.
Q
Who care
8:20 - Scaler subquery ( only fetches 1 row & 1 column
11:55 - Multiple Row subquery
type 1 - multiple row, multiple column
18:30 - type 2 - multiple row, single column
22:18 - Co-related subqueries
37:29 - Nested subquery
46:33 - Different Clause where subquery is allowed. ( select, from, where, having )
56:30 - SQL commands that allow subquery ( insert, update, delete )
Hi, i have a doubt, for multiple column subquey we were used in clause for fliter multiple colums but am using in clause in mssql am getting error so which clause can i use in mssql.
@@swathim2059 in ms sql u will have to use join clause , because where in will not work
SELECT e.emp_name, d.dept_name, e.salary FROM employee e JOIN department d ON e.dept_id = d.dept_id join (select dept_id,max(salary) as mx_salary from employee group by dept_id) as max_dept on max_dept.dept_id=e.dept_id and e.salary=max_dept.mx_salary;
instead of where in clause join the sub query table with the main query
Bro's a lifesaver
Since I started my new job I've been running away from subqueries. Whenever the query reached a certain level of complexity I referred it to the data scientist, this week I faced a challenge in which I will not be able to escape from the subqueries. I watched several videos and none reached this level of detail, mainly explanation. It will help me a lot. Thank you!!! Eternally grateful.
Man you are the most humble teacher i ever saw,
1) You make hard things look easy
2) You know exactly where people will have doubts and the pain of those ( Not only you revive that with solutions but also give precautions )
3) You explain things again and again without any hesitation or showing an attitude which i love the most! Everyone can see how much you want to make things understandable and easy for all.
I'm really looking forward for a ' Whole Advance SQL statements using Windows function and their applications' Video
Using Timestamps, extract, rank in those cases is quite tricky and I'm sure you are an Aid to those!
KUDOS To You With All The Respect!
Keep doing the work you do, no one else is at this level and Humbleness 🤜🏻
Hi Atharva ,
It’s really pleasing to read such feedback. Thank you very very much for your kind words.. truly appreciate it and I am so happy to know you find value in my contents 🙏🏼🙏🏼🙏🏼☺️
I haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. Kudos to you man! I can't thank you enough. Magnificent content!!!
SQL learners need a teacher like you.
Thank you Rohit. glad you liked it :)
True
@@techTFQ u r such a true teacher.. who explains so clearly..n all concepts r actually easily understandable..learning so much from ur knowledge n way of teaching n explaining..thanks a lot...
Thanks a lot Rooja 🙏🏼
One of the finest tutor I have ever seen, Going with the pace where the every concept is covered within a short time.
You are one of the best SQL tutor👍 I have seen. The best part is, you take one topic and cover everything in that (Full meals 😁). No need search any other content. Super helpful... Bro!
Thank you Vedantha for this amazing feedback :)
Really motivates me to make more :)
I have always used ORM tools to generate SQL queries, since I was afraid about complex queries. After watching your video three times, I feel quite confident to go deeper and write down complex native SQL commands. I wish you the best for such a thoughtful content!
Thank you Mahdi :)
Yes!! So glad you covered this in such a detailed video. Can’t wait to watch! Thank you so much!
Your welcome Laura 🙏🏼
Do give me your honest feedback after you watch the video..
@@techTFQ I enjoyed it! I do have some thoughts tho but it will take me time to compile those and send. It was a crazy day. I think subqueries are the hardest thing left for me in SQL except that I need to get more practice with window functions and also the with clause. But for subqueries I always feel like there is so much more to them. This video is helpful but I’d like to see a more general one to explain the various use cases for the placement of subqueries in the various clauses. Your real world experience such as when you mention that this is something we do all the time or this is something that is really not recommended to do and then you explain why - those comments are very helpful bc I’ve never been in this field and you are a pro so those pro tips are so valuable. So the use cases for the different placements in select, from, and where and also the performance for those for me would be also useful to have a solid understanding of. You did explain use case and performance in some detail for correlated subqueries in this one which was very useful, but I personally would like to have a better understanding of when the subquery in the from vs select vs where clause is the best choice vs forgetting the subquery altogether and going with the with clause. So, in the real world how often do you use subqueries vs the with clause and best use cases for each. Maybe a video to encompass all of that. And I think that making it very clear that any subquery in the from clause must have an alias is something that beginners don’t necessarily always know so that could perhaps be emphasized. I love that you spent an hour explaining subqueries but I could see another video stepping back in more general terms being also very useful. I hope this was helpful. Thanks for all of your good work. I also like the new background. 😊
@techTFQ I also want to add that I’m honored that you asked for feedback. I’m not sure if that was what you were looking for. I hope you have a nice night. 🙏🏻
Hi Laura, I loved this feedback. Kind of gave me an idea for a future video which should clear your doubts about where to use subquery and when to use it. And not just subquery but in general how to apply any SQL concepts when solving problem using SQL.
But first let me thank you for this amazing feedback because I am sure this took you a very long time to compile and write them down. I completely understand your point of view and its something that a few others have asked me as well.
But the thing is that, the only way you can clearly understand "where to use a subquery and when to use it and what concept of SQL to use and not use when solving a problem", all these will get clear only with experience. You need to solve many many queries, only then you will get an understanding of what to use when.
But yes, I can frame some tips and tricks which can give you a better idea of what subqueries to use and where to use them and when to use them based on what kind of requirement.. Just that I can explain these by hopefully solving different types of SQL queries.
Let me try to plan a video revolving this and come up with some tricks and tips.
And thank you for noticing the new background 😀
Bought a new camera and some lights and just starting to learn how to make better videos. Hopefully my video visuals and editing will get better with time.
Have a great day Laura and Thanks again for your amazing suggestion 🙏
@@techTFQ Thank You for taking the time to write such a detailed reply, Thoufiq. I’m so glad the RUclips algorithm finally got some sense and started steering me to your channel. I’ve learned so much from you and I really appreciate your encouragement and guidance. It’s true what they say: You are the best SQL teacher on RUclips. I can’t believe how your subscribers have grown since I found you at under 7K. This is a testament to your knowledge and experience and not only that but your way of explaining and your patience and clarity too, along with humility, which is rare and valuable as well. I hope and trust that all of your aspirations will become reality! And thank you again for your hard work. I’m so glad the feedback was helpful. I do so appreciate your feedback and tips for me too. Have a great night and hugs to your family 🙏🏻😀
The BEST video on subqueries on this platform. What a stuff this man have put in here. Legend!
The simplicity with which you broke down complex topics is a sheer gift
Keep it up
Never come across a better SQL teacher like you, every point sooo detailed! This is like a goldmine for SQL. Kudos to you for curating such awesome content!!!
A much needed one. Subqueries are the ones which i encounter the most in my work. And this video really helped me understand it even more clearly. Thank you so much Brother for yet another helpful video🙏🏻🙌🏻 👏🏻
I am glad you liked it bro 😍
Thank you ☺️
Please what kind of work? I hardly find jobs where you have to make constant queries to a database.
The best subquery video I’ve come across on RUclips. Simple and easy to comprehend.
Thank you Hafeez. glad you liked it :)
I had learnt sql from various sources...but no one can match up the with your tutorial. And I prepared for the job by watching your tutorial and successfully got the job.
Superb! Thank YOU Taufiq for sharing this video. I could not find a better explanation of correlated subquery anywhere else. Now, I am very clear about this topic. I appreciate all your help!
I am glad to hear that Rajiv :)
Best Teacher with best Teaching methods and explaining each n every topic.
Amazing content 😊👍
Thanks a ton Surbhi
Best SQL content so far. You have made sql easy and understandable
I think what's most effective about your tutorials is you don't just execute simple sql statements to illustrate a point, you actually provide real problems and demonstrate your "approach" with solving them--they're almost like "word problems" from primary school. 🤭 This has helped me understand greatly how to breakdown (future) problems.
Splendid Video Thoufiq !! 👏👏Neat and clear explanation of all the concepts.
Types of Queries you did framed to solve in this video is like a cherry on the cake.
Many thanks for considering our request and working on the same.
TechTFQ and Thoufiq will surely be my go-to channel/person for SQL hereafter.
I wish you should be recognized and appreciated by many subscribers very soon. Please keep doing the great work 😊🙏
Thank you so much Kaviya for your amazing feedback :)
I truly appreciate you taking time to leave this feedback.
I am so glad you liked the content and than you so very much for your kind wishes :)
@@techTFQ Pleasure is all mine 😊🙏
Bhaiya, your videos are just awesome. I was not good in sql. But I have started clearing interviews. Explanation is very nice and clear. Keep it up. Now step by step I will try to do it. Windows function video was really helpful. My concept is clear now.
Thank you Jayeeta for your kind words :)
Really happy to know my videos have helped 🙏
I can download this video a thousand times just because of the quality of the presentation!. Good work bro.
I've been having such a hard time with subqueries on how to write them, understanding them, etc. You've helped a lot thanks.
Hi. Your videos are great. Thanks for the effort you are making just for us to learn. I really appreciate that. While solving the UPDATE related question, I think the answer should be this:
update employee e
set salary=(select max(eh.salary) * 0.1 + e.salary
from employee_history eh
where e.dept_name=eh.dept_name)
where e.dept_name in (select dept_name from department where location='Bangalore')
and emp_id in (select emp_id from employee_history);
best content quality on this plateform ....amazing sir and god bless you sir and thanku so much for sharing your knowledge so deeply and nicely...it really helped a lot of students
Thank you Shaffu 🙏🏼
Glad you liked it
You are really good. I go through all your SQL videos whenever I need to go for interviews, and it always works for me. Here I am back again to watch your videos as I am in middle of a job search! Wish me luck. Thank you so much for these wonderful videos, they are the best!
Thank you Sneha and wishing you all the best 👍
Your explanation is amazing, really love watching your videos as I get to learn so much and they’re not boring either. Thank you so much for sharing this. Keep up the amazing work. 👊🏽👍🏽
Thank you so much for your valuable info on subquery.
Your welcome Sambit 🙏🏼
Sir, could you please make a video on elucidating when to use Join over Sub queries or vice-versa? Indeed, this is one of the major drawback for the beginner like me to deal with it in the initial stage.
super explanation haven't seen explanation of this kind anywhere, even paid courses fail to teach this way. Kudos to you man! I can't thank you enough. Magnificent content!!! Brilliant tutor thank you
SQL learners need a teacher like you.
Well Explained. Looking at these queries after a decade, I was searching for something which would explain in a simple language and w/o confusing! Kudos to you... 👏👏👏
This is an amazing video..thank you for helping students like me!!!!!!!!!❤❤❤❤❤❤❤❤
I am so glad to hear that Shiv ..
Thanks for your feedback and I am glad this helped ☺️🙏🏼
Subqueries are so tricky to me but this is by far the most detailed and comprehensive explanation on subquery that I've seen! Thank you soooooo much for that. Quick question on the total sales example, shouldn't we use SUM(quantity * price) to get total sales instead of just SUM(price)?
I'm guessing the price column represents total price and not price per unit.
This is a greatest ever video about subqueries, very easy to understand and well explanation. Thank you so much!
Awesome. Explained in such a simple way, even a person who is new to SQL can understand very well!!!
Hi , what an amazing effort!! THNAKS A MILLION!! Totally worth it. Also, kindly share the content notes if you have created for these stuff as it will be easy to practise alongside
Thank you :)
I have shared the dataset and all the scripts in my discord. link to join my discord is in video description
thank you so much for clear explanation
Your welcome Haritha 🙏🏼
Glad you liked it
Thank you a lot! Your explanation is much more clear than the explanation from my online course. God bless you!
your teaching is much better than many celebrity trainers, great teaching, thank you for wonderful explanation
thanks a lot for the videos, you explain it very well!
Glad you like them bro!
Most awaited video I was waiting for...thank you sir.. following you on LinkedIn ❤️
Thank you Pankaj 🙏🏼
I hope you like this one
@@techTFQ yes sir it helped me a lot ❣️
I am glad to hear that ☺️
You are just the best, just know that i am learning alot from you. Much respect from NIGERIA
Best teacher for learning SQL ...in RUclips . Thank you guru ji 🙏🙏🙏
Sir please make the video on sql store procedure and trigger...
Sure bro will do it
Thalaiva u r great... learned more thanks 🤞🙌
haha Thank you buddy 😍
oh boy❤ hands down the best advance sql tutorial i have seen in hundreds of sql related lesson. this is the bread n butter of what sql is all about. thank you 🙏 buddy for putting this out. Super informative and essential. 🍸
Thank you for the amazing content.
Your welcome bro. glad you liked it
Saving this to my folder and I am sure I m going to watch this multiple times... Going to be very useful... Thanks
Btw, could you have a video explaining how to analyze the performance of different queries that accomplish same result but performance is different due to the way it is handled
Thank you Vela :) I hope you like it.
As for performance of different queries, ill make separate video about it.
@@techTFQ certainly, like all the videos you make. It really shows how much efforts you put into it to make it very clear to everyone. I'm in no way connected to sqls at work, but got interested at after seeing your videos.
Amazing vela , I am so glad to read this ..
Thank you 🙏🏼
I was searching for correlated query and accidently landed on this video, I absolutely love the content and the way of explaining things is simply awesome💯💯
Take a bow to these guy for him amazing work. Thank You Toufiq for all your videos. Please continue your work. will always support you
Nice explanation...Helpful content...👍👍👍
Thank you Ambar 🙏🏼
Amazing 👏
Thank you 😍
i have gone through hundreds of videos on Joins but unable to get the knowledge. With just one video i became expert in joins :) this is because of you sir . Keep up the good work. I will be following your videos going forward. excellent explaination without any confusion.
one of best sql course in youtube. thanks
well detailed and great explanation of an important topic in SQL!
I think there might be a mistake in the question with the number of units sold by each store.
the average is (3+2+11+6)/4 and is far greater from 2 which you remarked :)
well noted! even i made that observation
Yes, I dont know why he made this mistake because in the avg(prive) exercise he said that you have to group them and then calculate the avg().
@@cypherecon5989 in that example he said across all stores
I also observe that as well 👍
I think that's a mistake
We should be left with 2 stores
Yes ! As the avg price for all stores coming as 2200.0
Sir, please provide us with the same table are you using during the written query.
Thanks for uploading this amazing video.
Yeah I’ll share all these scripts in my discord server .. so you can join my discord using the link in video description
Ok Thanks again 👏
@@techTFQ Sir,when you share the script on Discord server. as I just checked I didn't found out script there
It’s there in announcement channel..
U can ask other in discord if u can’t find it
this tutorial is so good, I fully watched it and still wanting more!
u r a good teacher ..happy that i found ur channel
@ 1:06:06 , in line number 164, I think it should set salary = (select salary + (max(salary*0.1))
Each Employee's salary should be incremented from his own salary and not from the maximum salary. The maximum salary is to be considered only for calculation of the increment.
Am I right?
Nope.. the question was to provide increment based on the max salary not considering each employee salary
@@techTFQ , so based on your logic, Sir, everyone from a department will be having equal salary, right?
Exactly
You are a great teacher 🎉 wonderful method of teaching 😀 pls try to be Active on this platform because a teacher like u is need for everyone..
If u know sql data tools kindly post it when u r free..
Thanks for the great work♥️
Because free education is the world's best education 🎈
Thank you Nisha ☺️🙏🏼
I am glad you value my contents so much .. and yeah I want to post more video on RUclips.. working towards it..
May I know what u mean by data tools?
@@techTFQ Data tools like SQL SERVER INTEGRATION SERVICES (SSIS), ANALYSIS SERVICES(SSAS),REPORTING SERVICES (SSRS).
Thanks for your reply 🎈
Oh ok got it..
Noted and let me consider the suggestion
@@techTFQ Thanks 😊
This video is far far better than any videos in entire youtube
Fantastic! This video even helps professionals SQL writer like me who needs a quick refresher!
Timestamp 55:30
The average will be 5.5 (11+2+6+3/4) .
But the query resulted as 2.2.
Any thoughts?
Even I have the same thoughts
It is 2.2 because it is the avg of quantity from the entire sales table..
You avg of 5.5 is the avg when considering the group by query ..
Difficult to explain in comment but if you read the query statement properly and try it out yourself, you will come to know
Thanks for the explanation 🙌
@@techTFQ True but it defies logic, if we have multiple entries of the same store, you cant take average without summing from individual stores, moreover, this cant be just ignored because it is for learning purpose, the query will become more involved, to a more attentive student current case seems confusing
I noticed this right away as well. I am very much an SQL newbie but the average of 2.2 is taking the full sales table into account with multiple store rows and not aggregating the total sales for each store. It is incorrect. You have to take the average using the group by clause.
Please can you share the dataset
I have shared the dataset in my discord channel.
you can join my discord using the link present in video description
Excellent Explanation.Thank You
One of the most Comprehensive video on subquery.. Really appreciated the efforts you put on to make understand complex topic really well.
where were you my angel? I have attended multiple trainings for SQL but nobody explains the concept in the way you do!! Quality content with practical examples. Hats off to you for helping so many IT professionals to understand SQL in depth.
Wanna ask what system management did he use in this video and what do use ?
@@rainbow100_ Pg admin on postgres
Thank you for making concept clear. Can't find a video better than this.
You are simply the best here on RUclips. Thank you so much no one does it better than you. Your videos has really helped me
one of the best tutorials I have ever learned. Thank you so much.
THE BEST SQL TUTORIAL AVAILABLE ON YT 💯💯💯
Given clarity among different joins thanks alot
This guy is an absolute champion! Thank you so much 🙏🏼
Thanks for this video, great work!
Well organized tutorial with great examples. Awesome
It is one of the best video classes, I learn something in such a short time. Everything is clearly explained by you regarding the subqueries. Thank you for the videos
A Big thanks to you. you make hard thinks easy in a single video this is extremely appreciable sir. thanks a lot...your explaining each and everything without timewasting short and easy I'm not gonna forget subqueries in my whole life after watching these. this free video in my paid course i didn't learn like these again thanks a lot.........
It's impossible to have a better video than this one about subqueries u are the best sirr
Thank you :)
Glad this helped
So beautiful and well thought, thanks.
Very useful and important video. Thank you so much Toufiq!
Good job! Thank you very much!
Almighty has saved my time in exploring here and there for learning sql and landed your youtube channel directly.
very nice explaination bro Toufeeq
super video on SQL subqueries . A complete take on subqueries with practical examples. Definitely the Best Video on whole youtube even better than paid classes .
I'm just in 1/3 of this video but I need to smash like button right now because your video perfectly teach in easy way many concepts I thought were much harder to learn. Keep doing content like this brother.
Man...what an explanation. I have attended paid courses for SQL but never understood the concepts so clearly. Thank you so much and i will be soon joing your 3 Aug batch.
Glad you liked it 🙏🏼
Excellent video! I learned due your guidance, that as first step, I can create 2 queries and than just combining them into a sub query, thank you !
Side note - I tried to learn sub queries a lot, this is the only vidoe which helped me understand the logic.
Thank You, This tutorial cleared my sub query doubts
First of all thanks to explain in deeply in this concept.
You make hard things to teach in easy way.
Hats off to that.
Very well explained! Thankyou
Best ever video on subqueries . Thanks a ton:)
it was a amazing , that shows your deep understanding of over these commands
Thanks a lot for your videos helping me get into a strong grasp of SQL fundamentals.
superb lesson, friend. Very thoroughly explained with beautiful business examples. Thank you!
Very nice video on subquery - explained very well each subquery
U are the best tutor I have never seen in my life
Thanks a lot for this long video.. very well explained and covered all topics.. it’s very clear and helpful for my interview
comprehensive, easy to follow! amazing!
Thank you :)
IT IS ONE OF THE BEST VIDEOS ABOUT SUBQUERY. THE EXPLANATION WAS CRISP AND CLEAR. THANKS FOR ALL YOUR EFFORTS
Thank you Damodar.. glad you liked it