Act fast, special offers end soon! Up to $294 is savings when you get Premium today.
Get offer codes

Ordering in SQL INNER JOIN output

Screen Link:
https://app.dataquest.io/m/179/joining-data-in-sql/7/combining-joins-with-subqueries

My Code:

SELECT f.name country, c.name city FROM cities c
INNER JOIN facts f ON c.facts_id = f.id

Why does the output of the above INNER JOIN maintain the order of facts_id in cities and not id in facts?

In other words, the INNER JOIN looks first at facts_id in cities , then finds the corresponding id in facts and performs the join. Why not the other way around?

1 Like

Hi @aditya.rawal23,

The reason it is looking first at facts_id in cities even if you reverse the order of tables in the query is that the query optimizing system of SQLite determined it to be the best course of action.

The default order of the nested loops in a join is for the left-most table in the FROM clause to form the outer loop and the right-most table to form the inner loop. However, SQLite will nest the loops in a different order if doing so will help it to select better indices.

If you would like to understand it in depth, then here are some good reads:
https://www.sqlite.org/eqp.html
https://www.sqlite.org/optoverview.html#table_order

Best,
Sahil

1 Like