Intermediate Joins in SQL; part 4 joining with subqueries

,
SELECT  al.title album, ar.name artist, sum(il.quantity) tracks_purchased
FROM invoice_line il
inner join track t on t.track_id = il.track_id
inner join album al on al.album_id = t.album_id
inner join artist ar on ar.artist_id = al.artist_id
group BY 1,2
order by 3 DESC
LIMIT 5

The above is my code, and it outputs the correct results. no subqueries.

Below is the answer code, with subqueries.

SELECT
    ta.album_title album,
    ta.artist_name artist,
    COUNT(*) tracks_purchased
FROM invoice_line il
INNER JOIN (
            SELECT
                t.track_id,
                al.title album_title,
                ar.name artist_name
            FROM track t
            INNER JOIN album al ON al.album_id = t.album_id
            INNER JOIN artist ar ON ar.artist_id = al.artist_id
           ) ta
           ON ta.track_id = il.track_id
GROUP BY 1, 2
ORDER BY 3 DESC LIMIT 5;

For all practical measures for the result (answer the posed question) they are equivalent. for the purposes of the lesson it’s required to used subqueries.

My concern is; time and again I find myself finding easy ways to form things with no subqueries, and I am wondering if that’s a good habit or not.

edit; figured out I had additionally mislabeled tracks_purchased as quantity and artist as artist_name. These errors have been rectified.

In this example, the 3 tables being joined in the subquery have a foreign key that can be used to join them to the table outside the subquery, so you certainly could get by just joining everything without the use of any subqueries. There are situations where you might have to use a subquery, though (for instance if you wanted to filter or apply some special conditions to the table within the subquery.


I like Trendfischer’s answer (one with bolded headings).

Personally, i prefer subqueries for logical organization, but WITH clauses make them even more readable/debuggable by pulling the subquery out of the nest to the top of the sql block.
Imagine what happens when you write sql pipelines.

I am currently imagining cascading errors… please no.

So, yeah, learn with clauses thoroughly, understood.

really helpful ! I was thinking about it in the whole morning, why should we use subquery here.