When to Use a Subquery in SQL

[@DatabaseStar]

Want to easily remember the SQL commands for your database? Get my free SQL Cheat Sheets here: www.databasestar.com/get-sql-cheat-sheets/?

[@willenaraines4596]

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!

[@DatabaseStar]

Thanks for the feedback, glad you found it useful!

[@adebanjomoyosore5721]

Honestly, I have often wondered why we needed subqueries and nobody has explained it to me perfectly like this.

[@Pandodora]

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!

[@DatabaseStar]

I’m so glad that this video was helpful for you! Subqueries can be hard to understand

[@buraktiras93]

Your videos are extremely helpful. Clean and clear. Thanks for the content, waiting for more SQL videos!

[@DatabaseStar]

Thanks Burak, glad you like the videos!

[@Nikopup]

The tips on breaking it down into simple steps and then bringing it all together really helps me a lot! Thank you ❤

[@DatabaseStar]

You’re welcome! Glad this process was helpful.

[@normn0]

Excellent video. As someone from a different STEM industry, its amazing how much trash you need to sift through on KZbin to find something like this in programming.

[@DatabaseStar]

Thanks a lot! I'm glad you find my video is one of the good ones!

[@marie-sanna]

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.

[@DatabaseStar]

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.

[@zcecmsh99]

You make the best SQL videos on the internet, thank you for making this so clear and concise - keep up the great work!

[@DatabaseStar]

Thanks a lot! I'm glad you like the video, I've been trying to make each video a little better than the last.

[@ASharonOkech]

This helped me understand subquery. I couldn't understand subquery before this video.

[@DatabaseStar]

Glad to hear it helped you understand it!

[@nathan.bradley]

Thank you for helping me finally getting this. The way you broke it down made it so much easier to understand. Thanks again!

[@DatabaseStar]

Glad it helped!

[@dhairyapanjwani1456]

finally cleared all the doubts in mind and got to know the mistake i was making!!!! Thank you for this video, amazing!

[@DatabaseStar]

Glad it was helpful!

[@adrianmora4481]

This is great!! for the longest time, I was looking a logical explanation for subqueries

[@DatabaseStar]

Glad it was helpful!

[@GmD.28]

Thank you so much ! I’ve been finding it hard to get my head around this topic

[@DatabaseStar]

Glad it was helpful!

[@Heli-b9s]

Very well explained, thank you!

[@DatabaseStar]

Glad it was helpful!

[@Manisha70856]

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

[@DatabaseStar]

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

[@MultiGrapewine]

I was confused why to use subquery when we can achieve same result set from joins. now it is clear

[@DatabaseStar]

Glad it was helpful!

[@tempMahad]

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

[@DatabaseStar]

You might find my video on "eCommerce Database Design" helpful, which caters for products that can have variations like this.

[@d_lydian]

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!

[@DatabaseStar]

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/

[@gggeeh]

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;

[@DatabaseStar]

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)

[@gggeeh]

@@DatabaseStar hey thanks for your reply, it helps!

[@douaz6905]

That was really helpful !

[@DatabaseStar]

Glad it helped!

[@utkarshgupta6091]

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

[@DatabaseStar]

Great idea! I can do some videos on those.

[@utkarshgupta6091]

@@DatabaseStar thank u. Would be of great help

[@jwsc9578]

So well explained❤

[@DatabaseStar]

Thanks!

[@dawitworku821]

Very helpful. Than you.

[@DatabaseStar]

Glad it helped!

[@arunareddy3491]

Clear explanation

[@DatabaseStar]

Thanks!

[@mostafa2199]

thanks man it really helped

[@DatabaseStar]

No problem!

[@icecube8496]

Hi. Are you left handed? Just curious.

[@DatabaseStar]

No, right handed actually. Is there something in the video that makes it seem like I am left handed?

[@olufemiolawale3848]

Thank you sir.

[@DatabaseStar]

You're welcome

[@user-yh9vd9yz9u]

Thank you!

[@DatabaseStar]

You're welcome!

[@Pankaj-Verma-]

Thanks.

[@DatabaseStar]

You're welcome

[@X1SX]

Thank you

[@DatabaseStar]

You’re welcome!

[@patientson]

Bad teaching... teach the art of applying subquery to a table and two tables. No tip was helphul at at all.

[@DatabaseStar]

Thanks for the feedback! Is there a better way that you would explain it?

[@adebanjomoyosore5721]

Thank you!

[@DatabaseStar]

You're welcome!

[@nrk07]

Thank you

[@DatabaseStar]

You’re welcome