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

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

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

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
```
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)
```
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.

Method 2

No it does not.

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

Method 3

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).

Method 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.

Method 5

As many have said: The order does not make a difference in result or performance.

What I want to point out though is that LINQ to SQL only allows the first case!

Eg, following example works well, …

var result = from a in db.a
             join b in db.b on a.StatusID equals b.ID
             select new { Name = a.Name, Status = b.Status }

… while this will throw errors in Visual Studio:

var result = from a in db.a
             join b in db.b on b.ID equals a.StatusID
             select new { Name = a.Name, Status = b.Status }

Which throws these compiler errors:

CS1937: The name ‘name’ is not in scope on the left side of ‘equals’. Consider swapping the expressions on either side of ‘equals’.
CS1938: The name ‘name’ is not in scope on the right side of ‘equals’. Consider swapping the expressions on either side of ‘equals’.

Though not relevant in standard SQL coding, this might be a point to consider, when accustoming oneself to either one of those.

Method 6

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.

Method 7

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.

Method 8

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.

Method 9

ERROR: ON clause references tables to its right (php sqlite 3.2)

Replace this

LEFT JOIN itm08 i8 ON  i8.id= **cdd01.idcmdds** and i8.itm like '%ormit%'  

LEFT JOIN **comodidades cdd01** ON cdd01.id_registro = u.id_registro

For this

LEFT JOIN **comodidades cdd01** ON cdd01.id_registro = u.id_registro

LEFT JOIN itm08 i8 ON  i8.id= **cdd01.idcmdds** and i8.itm like '%ormit%'

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating