Reputation: 164341

What is the 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

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?

Upvotes: 1468

Answers (6)

net_prog

Reputation: 10271

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.

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.)

Upvotes: 240

Michał Powaga

Reputation: 23183

Does it differ between different SQL implementations?

Yes, Microsoft Access doesn't allow just join. It requires inner join.

Upvotes: 81

Kristen

Reputation: 4311

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

Upvotes: 61

Martin Smith

Reputation: 453908

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;

Upvotes: 43

palehorse

Reputation: 27526

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.

Upvotes: 1615

Quassnoi

Reputation: 425813

No, there is no difference, pure syntactic sugar.

Upvotes: 369

What is the difference between JOIN and INNER JOIN?

Answers (6)

Related Questions