+3 votes
1 view
in SQL by (20.3k points)

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?

1 Answer

+4 votes
by (40.3k points)
edited by
  • 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)

Related questions

+3 votes
1 answer
asked Jul 3, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...