LIMITED TIME OFFER: 50% OFF OF PREMIUM WITH OUR ANNUAL PLAN (THAT'S $294 IN SAVINGS).
GET OFFER

How Do You Decide Which Table To Start With When Joining?

Screen Link:

Answer:

SELECT
    il.track_id,
    t.name track_name,
    ar.name artist_name,
    mt.name track_type,
    il.unit_price,
    il.quantity
FROM invoice_line il
INNER JOIN track t ON t.track_id = il.track_id
INNER JOIN media_type mt ON mt.media_type_id = t.media_type_id
INNER JOIN album al ON al.album_id = t.album_id
INNER JOIN artist ar on ar.artist_id = al.artist_id
WHERE il.invoice_id = 4;

So the answer starts at invoice_line and then joins track, media_type, album, and then artist in that order. It basically flows from top to down in the schema.

I was wondering what if you went in reverse: start with artist and then join album, track, media_type, invoice_line. I tried this and got the same answer.

Is there a standard or best practice when choosing where to start and in what order to join? What factors determine the best order?

Thank you for your time!

Here’s saying it matters: https://bertwagner.com/posts/does-the-join-order-of-my-tables-matter/

Practically, it is easier to think about it by putting the table containing the unit of analysis on the left-most side so all the tables to the right of it only serve to enrich the subject with additional columns. Editing code later also would not have to touch the leftmost table which reduces errors.

So in the above example, if the analysis was about customers, start with customer table, if about albums, start with album table.

1 Like