Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 224
Even Mien Avatar asked Apr 24 '09 12:04

Even Mien


People also ask

Does order matter in a JOIN on clause?

The order of the conditions in the ON clause doesn't matter.

Does the order of joins matter for performance?

JOIN order doesn't matter, the query engine will reorganize their order based on statistics for indexes and other stuff.


1 Answers

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.

like image 197
Quassnoi Avatar answered Sep 20 '22 06:09

Quassnoi