Difference between JOIN and INNER JOIN

Question

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

User · Answer

INNER JOIN   JOIN  INNER JOIN is the default if you don t specify the type when you use the word JOIN  You can also use LEFT OUTER JOIN or RIGHT OUTER JOIN  in which case the word OUTER is optional  or you can specify CROSS JOIN   OR  For an inner join  the syntax is  SELECT     FROM TableA  INNER  JOIN TableB  in other words  the  quot INNER quot  keyword is optional - results are the same with or without it

User · Answer

Does it differ between different SQL implementations    Yes  Microsoft Access doesn t allow just join  It requires inner join

User · Answer

Similarly with OUTER JOINs  the word  OUTER  is optional  It s 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

User · Answer

They are functionally equivalent  but INNER JOIN can be a bit clearer to read  especially if the query has other join types  i e  LEFT or RIGHT or CROSS  included in it

User · Answer

As the other answers already state there is no difference in your example   The relevant bit of grammar is documented here   lt join type gt               INNER       LEFT   RIGHT   FULL     OUTER          lt join hint gt          JOIN   Showing that all are optional  The page further clarifies that      INNER Specifies all matching pairs of rows are returned  Discards   unmatched rows from both tables  When no join type is specified  this   is the default    The grammar does also indicate that there is one time where the INNER is required though  When specifying a join hint   See the example below  CREATE TABLE T1 X INT   CREATE TABLE T2 Y INT    SELECT   FROM   T1        LOOP JOIN T2          ON X   Y   SELECT   FROM   T1        INNER LOOP JOIN T2          ON X   Y

User · Answer

No  there is no difference  pure syntactic sugar

[sql] Difference between JOIN and INNER JOIN

Examples related to sql

Examples related to sql-server

Examples related to join

Examples related to inner-join