Sunday, April 3, 2011

Difference between JOIN and INNER JOIN

Both these joins will give me the same results:

SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK

vs

SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK

Is there any difference between the statements in performance or otherwise ? Does it differ between different SQL implementations ?

From stackoverflow
  • No, there is no difference, pure syntactic sugar.

  • In your example, there is no difference.

  • See this question:
    http://stackoverflow.com/questions/448023?sort=newest

  • They function the same. INNER JOIN can be a bit more clear to read, especially if your query has other join types (e.g. LEFT or RIGHT) included in it.

  • Similarly with OUTER JOINs the word "OUTER" is optional, its the LEFT or RIGHT keyword that makes the JOIN an "OUTER" JOIN.

    However for some reason I always use "OUTER" as in LEFT OUTER JOIN and never LEFT JOIN, but I never use INNER JOIN but rather I just use "JOIN"

    SELECT ColA, ColB, ...
    FROM MyTable AS T1
         JOIN MyOtherTable AS T2
             ON T2.ID = T1.ID
         LEFT OUTER JOIN MyOptionalTable AS T3
             ON T3.ID = T1.ID
    

0 comments:

Post a Comment