MySql In an inner join does it matter which table comes first?

Question

I'm selecting data to output posts a user has book marked.

The main table which holds the ids of the posts a user has bookMarked, is called bookMarks. This is the table based on which posts will be selected from the posts table, to display to the user.

bookMarks

id  |  postId  |  userId
--------------------------
1   |  US01    |  1
2   |  US02    |  1
3   |  US01    |  2
4   |  US02    |  2

posts

id  |  postId  |  postTitle
--------------------------
1   |  US01    |  Title 1
2   |  US02    |  Title 2
3   |  US03    |  Title 3
4   |  US04    |  Title 4

My sql is currently like this:

select a.postsTitle
from posts a
inner join bookmarks b
  on b.userId = a.userId
  and b.userId = :userId

Notice, I have the table posts put first before the table bookmarks. But, since I'm selecting based on whats there in bookmarks, is it necessary I declare the table bookmarks first instead of post in the sql statement? Will doing it the way I'm doing it cause and problems in data selection or efficiency?

Or should I do it like:

select b.postsTitle
from bookmarks a
inner join posts b
  on a.userId = b.userId
  and a.userId = :userId

Notice, I have table bookmarks put first here.

In addition to m hasan's answer, by convention, where a WHERE clause is likely to place a condition on only one table, we tend to put that table first and then join the others onto it. But it's only a convention and makes no difference to the performance of an INNER JOIN — Strawberry, May 01 '14 at 12:30
Does this answer your question? [Does INNER JOIN performance depends on order of tables?](https://stackoverflow.com/questions/19612208/does-inner-join-performance-depends-on-order-of-tables) — philipxy, Apr 13 '20 at 23:54

score 8 · Accepted Answer · answered May 01 '14 at 13:10

Instead of the following:

select a.postsTitle
from posts a
inner join bookmarks b
  on b.userId = a.userId
  and b.userId = :userId

You should consider formatting your JOIN in this format, using the WHERE clause, and proper capitalization:

SELECT p.postsTitle
FROM bookmarks b
INNER JOIN posts p
  ON p.userId = b.userId
WHERE b.userId = :userId

While it makes no difference (performance wise) to MySQL which order you put the tables in with INNER JOIN (MySQL treats them as equal and will optimize them the same way), it's convention to put the table that you are applying the WHERE clause to first. In fact, assuming proper indexes, MySQL will most likely start with the table that has the WHERE clause because it narrows down the result set, and MySQL likes to start with the set that has the fewest rows.

It's also convention to put the joined table's column first in the ON clause. It just reads more logically. While you're at it, use logical table aliases.

The only caveat is if you don't name your columns and instead use SELECT * like the following:

SELECT *
FROM bookmarks b
INNER JOIN posts p
  ON p.userId = b.userId
WHERE b.userId = :userId

You'll get the columns in the order they're listed in the query. In this case, you'll get the columns for bookmarks, followed by the columns for posts.

Most would say never use SELECT * in a production query, but if you really must return all columns, and you needed the columns from posts first, you could simply do the following:

SELECT p.*, b.*
FROM bookmarks b
INNER JOIN posts p
  ON p.userId = b.userId
WHERE b.userId = :userId

It's always good to be explicit about the returned result set.

That `AND` in my question is where a `WHERE` actually is. I typed wrongly. — Norman, May 01 '14 at 14:03
@Marcus Adams do you have any reference to back up the case that INNER JOIN order is not important. I have been trying to look for reference but cannot find any to cite. — Nap, Jun 26 '19 at 04:57
@Nap, https://dev.mysql.com/doc/refman/5.5/en/nested-join-optimization.html - "For join expressions involving only inner joins (and not outer joins), parentheses can be removed and joins evaluated left to right. In fact, tables can be evaluated in any order." — Marcus Adams, Jun 26 '19 at 12:04

Mudassir Hasan · Answer 2 · 2014-05-01T12:34:13.443

There is no effect on query performance or final resultset with respect to placement of table on either side of JOIN clause if INNER JOIN is used .

The only difference observed is in order of columns returned and that too only if SELECT * is used . Suppose you have tableA(aid,col1,col2) and tableB(bid,col3)

SELECT *
FROM tableA
INNER JOIN tableB
ON tableA.aid=tableB.bid

returns column in order

aid|col1|col2|bid|col3

On otherhand

SELECT *
FROM tableB
INNER JOIN tableA
ON tableA.aid=tableB.bid

returns column in order

 bid|col3|aid|col1|col2|

But it matters in case of LEFT JOIN or RIGHT JOIN.

MySql In an inner join does it matter which table comes first?

2 Answers2