Join vs. sub-query

Question

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why.

I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a JOIN and therefore is there nothing to worry about?

Answer 1

• A LEFT [OUTER] JOIN can be faster than the subquery used for the same case because the server will be able to optimize it better.
• Therefore, subqueries can be slower than the LEFT [OUTER] JOIN, but its readability is higher as compare to Joins.
• Joins and Subqueries are used to combine data from the different table into a single result set. In which Joins are used to return two or more rows whereas, Sub-queries are used to return either a row set or a scalar (single) value. 

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

As Sub-queries are more recommendable to use you can refer to the example given below for your understanding.

SELECT OrderID, OrderName, List_Price, (SELECT AVG(List_Price)

 FROM Orders.Order) AS AvgList_Price

 FROM Orders.Order

 WHERE ListPrice > (SELECT AVG(List_Price)

 FROM Orders.Order)