Difference between JOIN and INNER JOIN
SqlSql ServerJoinInner JoinSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
No, there is no difference, pure syntactic sugar.
Solution 3 - Sql
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 "INNER" keyword is optional - results are the same
with or without it)
Solution 4 - Sql
> Does it differ between different SQL implementations?
Yes, Microsoft Access doesn't allow just join
. It requires inner join
.
Solution 5 - Sql
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
Solution 6 - Sql
As the other answers already state there is no difference in your example.
The relevant bit of grammar is documented here
<join_type> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
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;