Does the order of tables in a JOIN matter?

Question

When we write a JOIN query, does the order we specify the tables matter?
For example,
FROM table1 JOIN table2
vs
FROM table2 JOIN table1

Answer

Generally, no, the order of the tables in the JOIN will not affect the overall results of the query.

As long as you specify what columns to select, the results should appear essentially the same, just that the rows will be ordered according to the appearance in the first table.

However, if you do not specify specific columns, then the order of columns will be different depending on the order of the tables. Without specifying columns in your SELECT, by using just SELECT *, the result query will order the columns by the first table’s columns followed by the second table’s columns.

For example, say we had two tables:
orders, with the columns order_id, customer_id
and
customers, with the columns customer_id, address.

If we run the following query,

SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

the columns in this query’s results will be ordered from left to right as:
order_id, customer_id, customer_id, address

Conversely, if we ran this query,

SELECT *
FROM customers
JOIN orders
ON orders.customer_id = customers.customer_id;

it will return the columns in the following order from left to right:
customer_id, address, order_id, customer_id

If the order of your columns matters in the result query, then it will be important to keep this in mind.

24 Likes

Hi, I got a little bit confused in the exercise that comes right after - PRIMARY KEY VS FOREIGN KEY. I am supposed to perform the INNER JOIN in this task. But in this case, it seems like the order actually matters.

If I write the query this way:

SELECT *
FROM students
JOIN classes
ON students.class_id=classes.id;

It is not accepted and I get notification saying “Join classes to students.”

If I write it this way than it is ok:

SELECT *
FROM classes
JOIN students
ON classes.id=students.class_id;

I think I don’t fully understand why the first solution is not accepted.
Could anyone advise me please?

6 Likes

Hello :slight_smile:

Both solutions are correct. Codecademy tests sometimes are too strict. Thanks for bringing this to our attention, I will report this as a bug :slight_smile:

Order in INNER JOIN does not matter, both solutions are correct, this is a problem on our side.

8 Likes

In previous lessons maybe lesson 2, i used ‘AS’ keyword to print the table. My output and the answer was same but it was not accepted as i used ‘AS’ keyword. After removing it, my answer was accepted. Is that also a Bug.

Data Science Path
Lesson 3, Multiple Tables Lesson 5