When to Use a Subquery in SQL
Vložit
- čas přidán 28. 07. 2024
- 📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn and become confident in SQL: databasestar.mykajabi.com/sql...
How do you know when to use a subquery?
Subqueries in SQL are a handy feature that let you use the results of one query as input into another query.
They can simplify your query and make it easier to maintain. But how do you know when to use a subquery instead of other methods, such as joins?
In this video, you’ll learn what a subquery is, when you should use a subquery, a simple example of a query, and transforming it to use a subquery to solve a problem.
You’ll also see when you should use a subquery or a join, as well as several other tips for working with SQL subqueries.
Timestamps:
00:00 What you’ll learn in this video
00:34 When you should use a subquery
01:41 A step-by-step example without a subquery
03:56 Using a subquery
05:06 Subquery vs join
06:43 Tips for working with subqueries - Věda a technologie
You are the only person that has clearly explained why and how a subquery is used. I couldn't figure out what the benefits would be. Thank you!
Thanks for the feedback, glad you found it useful!
Honestly, I have often wondered why we needed subqueries and nobody has explained it to me perfectly like this.
Your videos are extremely helpful. Clean and clear. Thanks for the content, waiting for more SQL videos!
Thanks Burak, glad you like the videos!
Excellent video. As someone from a different STEM industry, its amazing how much trash you need to sift through on CZcams to find something like this in programming.
Thanks a lot! I'm glad you find my video is one of the good ones!
Thank you for taking the time to actually explain WHY we need the goddamn subqueries; they seemed useless for me but now I'm starting to understand their importance and relevancy, and your video was super helpful for this.
Thanks! I tried to explain the "why": I've seen a lot of tutorials on this and they just explain the "how", but not why to use them.
The tips on breaking it down into simple steps and then bringing it all together really helps me a lot! Thank you ❤
You’re welcome! Glad this process was helpful.
Thank you so much ! I’ve been finding it hard to get my head around this topic
Glad it was helpful!
been traumatized by how complicated subquery is until I found your video. Very clear, straightforward and most importantly you explain such complicated query easily, that is indeed helpful, thanks!
I’m so glad that this video was helpful for you! Subqueries can be hard to understand
Thank you for helping me finally getting this. The way you broke it down made it so much easier to understand. Thanks again!
Glad it helped!
You make the best SQL videos on the internet, thank you for making this so clear and concise - keep up the great work!
Thanks a lot! I'm glad you like the video, I've been trying to make each video a little better than the last.
This is great!! for the longest time, I was looking a logical explanation for subqueries
Glad it was helpful!
finally cleared all the doubts in mind and got to know the mistake i was making!!!! Thank you for this video, amazing!
Glad it was helpful!
So well explained❤
Thanks!
thanks man it really helped
No problem!
Clear explanation
Thanks!
Very helpful. Than you.
Glad it helped!
Thank you!
You're welcome!
Thank you
You’re welcome!
Thank you sir.
You're welcome
I was confused why to use subquery when we can achieve same result set from joins. now it is clear
Glad it was helpful!
Thanks.
You're welcome
Hey there, I've got a question: I got a signals table with 2 columns (ParameterID & Time). Everytime a Parameter gets updated, it gets a new timestamp, so there are a lot of database row entries with the same ParameterID and a different timestamp. I want to query the latest timestamp of 3 specific ParameterIDs (33200, 33201, 33202). How can I achieve that in Microsoft SQL? Thanks for your help!
You could what's called a "correlated subquery" for this. You would use the subquery to find the maximum/latest timestamp for each of the parameter IDs, and then select the row that matches.
Here's a query that could work:
SELECT t1.parameter_id, t1.timestamp
FROM your_table t1
WHERE t1.parameter_id IN (33200, 33201, 33202)
AND t1.timestamp = (
SELECT MAX(timestamp)
FROM your_table t2
WHERE t2.parameter_id = t1.parameter_id
);
You could also use window functions to find the max values, which I've written about here: www.databasestar.com/select-rows-with-max-value/
hello. could u please cover some topics of sql that are required for data science like window functions. These topics were not present in your udemy course. please it would be of great help
Great idea! I can do some videos on those.
@@DatabaseStar thank u. Would be of great help
hi thank you for your video, can i ask why is a subquery need in this practice solution:
SELECT
emp_no,
(SELECT
MIN(dept_no)
FROM
dept_emp de
WHERE
e.emp_no = de.emp_no) dept_no,
FROM
employees e;
Was wondering why I cannot use join like this instead:
SELECT e.emp_no,
MIN (de.dept_no) as dept_no,
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no;
Both queries should show the same results so I don't see how the first one needs to use a subquery. The second query will actually return an error as it does not have a GROUP BY, but once you add GROUP BY e.emp_no, they should be the same. (I haven't tested these queries so I could be wrong)
@@DatabaseStar hey thanks for your reply, it helps!
Hello, I got a question.
I'm building a system for my organisation and this is what they do. They sell products which have varying prices depending on quantity. For example prestige 1kg is 130 but prestige box 10kg is 1,050. Please help me on how to create a database that can help with variations. Thanks
You might find my video on "eCommerce Database Design" helpful, which caters for products that can have variations like this.
Select product_name from products where price > avg(price).
Could you please confirm that can we write like this to find all the products having price greater than avg if products
Unfortunately that query won't work, but it makes sense and it would be helpful if it worked that way!
The WHERE clause can't compare a value for a row against the result of an aggregate function like this. However, you can use a subquery like in this video to write it, to calculate the AVG in a subquery:
Select product_name from products where price > (SELECT avg(price) FROM products);
Hi. Are you left handed? Just curious.
No, right handed actually. Is there something in the video that makes it seem like I am left handed?
Thank you!
You're welcome!
Thank you
You’re welcome