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!
This is really helpful; more so than my online course! I look forward to watching more of your videos. Thank you!
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.
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
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
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!!!!
super helpful and you explain stuff in a way where you don't rush through it but also don't take ages on it.
Absolutely amazing explanation and you give hope that you said it took you a while, now look at you.
Your SQL knowledge is admirable.
So easy to understand without broken English. Keep them coming!
I bought a course on Udemy, you solve like 10 doubts I had in just 1 video
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
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!
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....
Really great explanations. Easy to follow and perfect variety. Very much appreciate you posting these tutorials, they're beyond helpful. Thanks!
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.
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.
Where the heck have you been? You’re the only useful person on UA-cam!
Thank you for sharing, I feel stupid not understanding, but you kind of make it clearer
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
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
Brilliant explanation The online course I took had me lost at this part.
Actually for me this makes amazing sense. Thanks for the tutorial.
Thank you, a great explanation and examples for subqueries.
Thank you for explaining it the way you did, it just clicked for me. Great stuff! Keep up the good videos.
A great, and much needed take on this subject.
Thank you for summing up subquery. Thank you.
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.
Thanks for the tutorials, you have being of great help on this journey...Keep them coming
Awesome explanation. Thank you very much. I was completely lost on subqueries.
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.
And The search ends here.... Perfect 💯
you just helped me through my project in SQL, thankss a lot!
Great videos, if you ever update maybe consider adding a use case with each example?
This is an excellent video. Thank you!
Really good explanation. I will be glad to share this with developers who write "expensive" queries with complex join statements.😀
Thank you! You look so positive and warm person :)
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.
I like how you explain, keep up the good work.
Thank you for this brilliant video. Please upload more :) :).
Simple and direct. Just what I was needed.
great videos! Its soo much easier too understand when watching your tutorials! Please do more advanced videos with window functions etc.
What does the 1,2 mean in the Group By 1,2?
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?
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.
You sir are such a great teacher. Thank you
Great video! Thank you so much for bringing a clear explanation of this topic. I look forward to viewing your other videos.
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!
This is so helpful! Thank you!! Rec: time stamps for each example
Great video easy to understand
explained very well. thank you
Thanks for this sql series
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.
Thanks, great explanation!
That was great Explaination.
Well explained thanks a lot finally was able to get a better grasp of Sub Queries
I love the way you teach. You are clear and concise. How long have you been writing SQL?
Great explanation. Thank you :)
Thank you for your effort, keep moving
you are a champion thanks a lot..(im buying your book also!!!)
Thanks helped me out heaps!
Very helpful - thank you !!
Thank you for this video, think subqueries is slowly starting to sink in
You’re a real G
This video saved my ass, THANK YOU.
Great video, very comprehensive!
Lovely. Thanls a lot for this great share 👌🙂
Average number of orders per customer is a bit ambiguous as this could mean per specific customer instead of per one general customer.
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.
thank god for this video!
I am here wondering if another way to write the first example with freight as
SELECT AVG(Freight), customer_id
FROM orders
GROUP BY customer_id
Would give same result as the Subquery in the first example?
Thank you for this video
Awesome vid! thanks bud :)
Great explanation! Do you have a video for Correlated Subquery as well? It is super confusing
loved this!!!
thanks man, appreciate the explanation
Amazing Explanation Thank you 🙌
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
Isso eh curso pra não mancos THANKS!
Very helpful. Than you.
Thank you for the video
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)?
Quick question: what is the group by 1,2 doing? is that just ordering them by the order in which they columns were entered in the select statement?
Thank you! This helps me a lot.
Dude this was great.
comment for algorithim, cheers mate
Great explanation. Thank you!
I don't get it why need to use subqueries for the select clause average freight. Why can't just use avg(frieght)? Arent that the same thing?
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?
Thank you again
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).
Thank you bro man 💥
Great videos - keep it up!
Thank you, kind sir. This was essential.
Beginner here with some experience. I don't get the group by 1 , 2. I'm used to grouping by the field name. Is Group BY 1, 2 the same as typing Group BY customerID, shipcountry ?
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!
amazing man good job