Join vs subquery
I used an inner join and got the same answer. Why should we use subquery over a join? I am still new. Would there be a case of using a join and not getting the same answer.
1 answers ( 0 marked as helpful)
Hi Linda!
Thanks for reaching out.
In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster. The good thing in sub-queries is that they are more readable than JOINs. So, it is up to your choice. There are cases where you can only use a subquery. These cases are:
- Subquery in FROM With a GROUP BY
SELECT column_name_1, column_name_2
FROM
(
SELECT column_name_1, SUM(value) AS value_name FROM table_name_1
GROUP BY column_name_1
) AS s
WHERE value < 1500;
- Subquery Returning an Aggregate Value in a WHERE Clause
Example:
SELECT column_name FROM table_name_1
WHERE value < (SELECT AVG(value) FROM table_name_2);
Hope this helps.
Best,
Tsvetelin