Does the order of tables referenced in the ON clause of the JOIN matter?

Question

Does it matter which way I order the criteria in the ON clause for a JOIN?

select a.Name, b.Status from a
inner join b
on a.StatusID = b.ID

versus

select a.Name, b.Status from a
inner join b
on b.ID = a.StatusID

Is there any impact on performance? What if I had multiple criteria?

Is one order more maintainable than another?

Answer 1

No it does not.

What i do (for readability) is your 2nd example.

Answer 2

No. The database should be determining the best execution plan based on the entire criteria, not creating it by looking at each item in sequence. You can confirm this by requesting the execution plan for both queries, you'll see they are the same (you'll find that even vastly different queries, as long as they ultimately specify the same logic, are often compiled into the same execution plan).

Answer 3

JOIN order can be forced by putting the tables in the right order in the FROM clause:

1. MySQL has a special clause called STRAIGHT_JOIN which makes the order matter.

   This will use an index on b.id:

   SELECT  a.Name, b.Status
   FROM    a
   STRAIGHT_JOIN
           b
   ON      b.ID = a.StatusID
   

   And this will use an index on a.StatusID:

   SELECT  a.Name, b.Status
   FROM    b
   STRAIGHT_JOIN
           a
   ON      b.ID = a.StatusID
   

2. Oracle has a special hint ORDERED to enforce the JOIN order:

   This will use an index on b.id or build a hash table on b:

   SELECT  /*+ ORDERED */
           *
   FROM    a
   JOIN    b
   ON      b.ID = a.StatusID
   

   And this will use an index on a.StatusID or build a hash table on a:

   SELECT  /*+ ORDERED */
           *
   FROM    b
   JOIN    a
   ON      b.ID = a.StatusID
   

3. SQL Server has a hint called FORCE ORDER to do the same:

   This will use an index on b.id or build a hash table on b:

   SELECT  *
   FROM    a
   JOIN    b
   ON      b.ID = a.StatusID
   OPTION (FORCE ORDER)
   

   And this will use an index on a.StatusID or build a hash table on a:

   SELECT  *
   FROM    b
   JOIN    a
   ON      b.ID = a.StatusID
   OPTION (FORCE ORDER)
   

4. PostgreSQL guys, sorry. Your TODO list says:

   Optimizer hints (not wanted)

   Optimizer hints are used to work around problems in the optimizer. We would rather have the problems reported and fixed.

As for the order in the comparison, it doesn't matter in any RDBMS, AFAIK.

Though I personally always try to estimate which column will be searched for and put this column in the left (for it to seem like an lvalue).

See this answer for more detail.

Answer 4

No there is not. At the end of the day, you are really just evaluating whether a=b.

And as the symmetric property of equality states:

• For any quantities a and b, if a = b, then b = a.

so whether you check for (12)*=12 or 12=(12)* makes logically no difference.

If values are equal, join, if not, don't. And whether you specify it as in your first example or the second, makes no difference.

Answer 5

Read this

SqlServer contains an optimisation for situations far more complex than this.

If you have multiple criteria stuff is usually lazy evaluated (but I need to do a bit of research around edge cases if any.)

For readability I usually prefer

SELECT Name, Status FROM a 
JOIN b 
ON a.StatusID = b.ID

I think it makes better sense to reference the variable in the same order they were declared but its really a personal taste thing.

Answer 6

The only reason I wouldn't use your second example:

select a.Name, b.Status 
from a
inner join b
  on b.ID = a.StatusID

Your user is more likely to come back and say 'Can I see all the a.name's even if they have no status records?' rather than 'Can I see all of b.status even if they don't have a name record?', so just to plan ahead for this example, I would use On a.StatusID = b.ID in anticipation of a LEFT Outer Join. This assumes you could have table 'a' record without 'b'.

Correction: It won't change the result.

This is probably a moot point since users never want to change their requirements.

Answer 7

nope, doesn't matter. but here's an example to help make your queries more readable (at least to me)

select a.*, b.*
from tableA a
     inner join tableB b
          on a.name=b.name
               and a.type=b.type

each table reference is on a separate line, and each join criteria is on a separate line. the tabbing helps keep what belongs to what straight.

another thing i like to do is make my criteria in my on statements flow the same order as the table. so if a is first and then b, a will be on the left side and b on the right.