Last answered:

16 Nov 2022

Posted on:

14 Nov 2022

5

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)
Instructor
Posted on:

16 Nov 2022

3

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

Submit an answer