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

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.