How to do Subqueries in SQL with Examples
ฝัง
- เผยแพร่เมื่อ 6 ต.ค. 2024
- Step-by-step tutorial shows you how to use SQL subqueries in your SELECT statement & FROM and WHERE clauses!
Queries:
1) A subquery in the SELECT statement: SELECT customerid, freight, (SELECT AVG(freight) FROM orders) FROM orders
2) A subquery in the FROM clause: SELECT shipcountry, AVG(num_orders) FROM (SELECT customerid, shipcountry, count(*) AS num_orders FROM orders GROUP BY 1,2) sub GROUP BY 1
3) A subquery in the WHERE clause: SELECT * FROM orders WHERE employeeid IN (SELECT employeeid FROM employees WHERE LOWER(firstname) LIKE '%b%')
Get the Northwind Database: • How to Load a Sample P...
Support the Channel:
1) Subscribe:
2) DataCamp:
3) Udacity:
Finally, a break through. Finally, someone who explains the use of sub queries well.
The idea that a subquery in a FROM statement creates a table and starting the query process from there was a real lightbulb moment - thank you
CANNOT EXPLAIN ENOUGH HOW MUCH YOU SAVED MY LIFE.
Spending my Christmas binge watching your SQL tutorials haha so helpful. Thank you!!!!
Haha, sounds like a good Christmas to me!
Thank you for telling us that even when you first learned this it was complex, because holy cow! I cannot wrap my head around this. I can kind of grasp whats going on, but I think you know the confusion that goes through a noob's mind. Thank you for your tutorials! I plan on watching many more of your videos.
WOW! the way you explained that is like first doing a table to then going and making a query from that table was a breakthrough for my understanding of the concept. I really admire people who can transform concepts into more digestible material. Thanks
I am getting ready for an internship using SQL as a data analyst and this made a ton of sense, thanks! I will be subscribing
did you get the internship? just curious lol
This is really helpful; more so than my online course! I look forward to watching more of your videos. Thank you!
Your SQL knowledge is admirable.
super helpful and you explain stuff in a way where you don't rush through it but also don't take ages on it.
Just started a Business Systems Analyst entry level position that uses SQL everyday and this video has been so helpful! You explained it better than the corporate instructor, Khan Academy and my Udemy course! THANK YOU!!!!
I bought a course on Udemy, you solve like 10 doubts I had in just 1 video
Excellent explanation, these sub queries got me messed up but this video helped. Your great and explaining and hoping you succeed with your channel, you seem like a genuine dude.
Thanks a lot for the kind words, means a lot!
Really great explanations. Easy to follow and perfect variety. Very much appreciate you posting these tutorials, they're beyond helpful. Thanks!
Absolutely amazing explanation and you give hope that you said it took you a while, now look at you.
So easy to understand without broken English. Keep them coming!
Great video man!
The best explanation of sub queries that I found!
This is helping me a lot because I'm starting with a course of T-SQL
Very helpful, some key explanation that made it "click" with me. And most of all, I like that you confess to your struggle at the beginning, beacause I have my though times right now....
hope you would keep on making SQL tutorials (or other programming language). you help me a lot in my review for my assessment. you have explained each topic very well! I'm learning a lot from you.
you just helped me through my project in SQL, thankss a lot!
Awesome explanation. Thank you very much. I was completely lost on subqueries.
Thanks for the tutorials, you have being of great help on this journey...Keep them coming
Thank you for explaining it the way you did, it just clicked for me. Great stuff! Keep up the good videos.
This was very helpful. It saved me a lot of time. I understood it the first viewing and was able to apply the concepts with ease and complete my homework on time. Thank you, you do an excellent job. I will come back for future questions in SQL.
Thank you for sharing, I feel stupid not understanding, but you kind of make it clearer
great videos! Its soo much easier too understand when watching your tutorials! Please do more advanced videos with window functions etc.
There are also subqueries in join. It's hard to find a material as good as this. Leaving a like and comment to support your channel.
All your videos are very helpful and well explained! Helps me a lot in understanding SQL better. Are you planning on doing any more on SQL soon?
Thank you, a great explanation and examples for subqueries.
Brilliant explanation The online course I took had me lost at this part.
Actually for me this makes amazing sense. Thanks for the tutorial.
A great, and much needed take on this subject.
Hi there, I really liked the explanation, just a suggestion if you would give a very quick overview of the heads of the dataset so that it is easier to comprehend more how the query/subquery is extracting the data.
Thank you! You look so positive and warm person :)
This is exactly what I was looking for - thank you for sharing!
Does the internal table have to be the same as the outer table? "orders" in your example.
Where the heck have you been? You’re the only useful person on TH-cam!
I like how you explain, keep up the good work.
I didn't understand the concept one bit and I didn't understand why we should use them when I was in the university, but I understand now lol. Thanks for the tutorial. It's simple, yet really helpful for me
Thank you for summing up subquery. Thank you.
Really good explanation. I will be glad to share this with developers who write "expensive" queries with complex join statements.😀
overwhelmed?! tuh... i am terrified!
i came to you, from my online course, for some type of clarity.
what did I get you might ask. HUMBLED!
tfs this video anyway, I'm hopeful I will find some gems on your channel.
-your new (maybe in over her head) subscriber
Great video! Thank you so much for bringing a clear explanation of this topic. I look forward to viewing your other videos.
I love the way you teach. You are clear and concise. How long have you been writing SQL?
What does the 1,2 mean in the Group By 1,2?
You sir are such a great teacher. Thank you
Simple and direct. Just what I was needed.
This is so helpful! Thank you!! Rec: time stamps for each example
This is an excellent video. Thank you!
Thank you for this brilliant video. Please upload more :) :).
Well explained thanks a lot finally was able to get a better grasp of Sub Queries
It seems the subquery in the where clause is easiest to understand. SQ in the select statement is probably used to compare as it's gong to give only a single value irrespective of all the records. And finally SQ in the from can be used to create a temp table with aggregates; which will be used by outer query to do further aggregates.
And The search ends here.... Perfect 💯
Thanks for this sql series
Great explanation! Do you have a video for Correlated Subquery as well? It is super confusing
Thanks, great explanation!
Great explanation! What do the numbers 1 and 2 represent in the command "GROUP BY 1, 2" ?
what i wondered too
@@summer9380 My guess is referring to the columns in the select. As group be the first attribute then group by the second (cant remember the exact attribute names).
you'd think it'd be easy to find this info out there, but my online SQL course didn't cover it as well as you did. Thanks for the video!
was it udemy BA analyst course by any chance because i though the same thing haha
explained very well. thank you
Great video easy to understand
Great video, very comprehensive!
Great explanation. Thank you :)
Thank you for your effort, keep moving
Thanks helped me out heaps!
it should be a crime that universities get away with charging 50k of tuition for a shittier version of the stuff we learn for free. Really helpful!
Just a note for others; The first type of subselect, the way it was written will apply the aggregate of the whole table to each row. As far as I know all major versions of SQL support this.
It's useful if you need min/max/grand total at the table level. For instance;
select customerid, sum(freight) as subt, (select sum(freight) from orders) as grandt, subt/grandt from orders group by 1.
This will get you the customer ID, sum of freight by customerid, and sum of freight for all customerids, and the % of the grandt each customerId makes up.
Some versions of SQL support using this type of subselect to directly compute the subt. The modification would be;
(SELECT AVG(freight) FROM orders as o1 where o1.customerid = o2.customerid) from orders as o2. --This should achieve the same result as sum(freight) above, but my experience is not all versions of SQL are capable of understanding how to do this and will throw errors or bad results.
A final note. Subqueries are also very useful if your version of SQL doesn't support pivot and unpivot functions. What I have also found is almost any time you are creating a temp table you could probably be using a subquery in 1/3 the lines and a lot more clear.
Very helpful - thank you !!
That was great Explaination.
Amazing Explanation Thank you 🙌
loved this!!!
thanks man, appreciate the explanation
Dude this was great.
you are a champion thanks a lot..(im buying your book also!!!)
Awesome vid! thanks bud :)
Lovely. Thanls a lot for this great share 👌🙂
This video saved my ass, THANK YOU.
Thank you! This helps me a lot.
Great videos - keep it up!
thanks for the explanations. Why wouldn't you be able to take the average directly instead of needing to first subquery? Also, in the first subquery example, why do you need the subquery instead of writing SELECT AVG(freight)?
You’re a real G
Thank you bro man 💥
Thank you from Italy! :) Just a question: what does GROUP BY 1, 2 mean? It is the first time that I see it...
it refers to the order of the columns, i.e. 1 is customerId and 2 is shipcountry
Very helpful. Than you.
Hi Sir, can you make videos on how to interpret EXPLAIN statements, how each line of explanation tallies with each query or subquery, and how the fixes translates to the new EXPLAIN statements? Thanks for making this video by the way!
Great content! Thanks!
Thank you for this video
nice job!
Great explanation. Thank you!
Thank you for the video
Average number of orders per customer is a bit ambiguous as this could mean per specific customer instead of per one general customer.
amazing man good job
This helped, thank you!
thank god for this video!
Thank you for this video, think subqueries is slowly starting to sink in
AWESOME! thank you for your effort!
Question regarding the first example; could you not achieve the same result without a subquery by just adding avg(freight) as avg to the original select statement?
very helpful video. I just have a question, which one is better(most "performant") , doing a subquery or a join?
Thank you again
Great explanation...thanks!
Thanks Erin! Glad it was helpful to ya!
Why were you using Grouping By 1 and 2 in your subquieres?
Like how do WE make a Query of two cities in a STATION table with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
What about inside joins( Select........ )?
Also, where to find other good sources to understand subquery ?