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?

per the ANSI SQL 92 specification, they are identical: "3) If a is specified and a is not specified, then INNER is implicit." — DaFi4, Aug 27 '20 at 12:01

score 1524 · Accepted Answer · edited Dec 01 '14 at 16:35

1524

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.

edited Dec 01 '14 at 16:35

Sled

18,541
27
119
168

answered Feb 19 '09 at 14:50

palehorse

26,407
4
40
48

21

Is this true for all data bases (e.g. SQL, postgres?) Does anyone know a link to the documentation explaining this? – Chogg Oct 25 '17 at 17:32
20

It's ANSI SQL standard. See more: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt; https://en.wikipedia.org/wiki/SQL-92 – Indian Jan 26 '18 at 10:14
45

@Ivanzinho: Keyboard strokes are not the measure of query or program complexity. Real life complexity comes from maintainability, where readability plays a major role. The fact that when it says INNER JOIN, you can be sure of what it does and that it's supposed to be just that, whereas a plain JOIN will leave you, or someone else, wondering what the standard said about the implementation and was the INNER/OUTER/LEFT left out by accident or by purpose. – Tuukka Haapaniemi Feb 11 '20 at 09:52
4

Thanks @Indian for your links. The _key_ holds in page 181 of the first one, when describing the generative grammar of page 180: "If a is specified and a is not specified, then INNER is implicit." – Olivier Mar 09 '21 at 17:34

score 338 · Answer 2 · answered Feb 19 '09 at 14:48

338

No, there is no difference, pure syntactic sugar.

answered Feb 19 '09 at 14:48

Quassnoi

413,100
91
616
614

41

I wouldn't call this syntactic sugar. "Default" join type, "shorthand," or "alias," maybe. – mk12 Jun 24 '15 at 13:32
92

*In computer science, syntactic sugar is syntax within a programming language that is designed to make things easier to read or to express*. I believe ability to omit `INNER` falls under this definition. – Quassnoi Jun 24 '15 at 13:35
23

If you apply the definition very literally, yes, but I've always seen it reserved for more interesting types of syntax, not just alternative names for things. – mk12 Jun 25 '15 at 14:01
32

@Quassnoi the mere fact that this question is asked, shows the absense of `INNER` does _not_ make the query easier to read. For all I know, `JOIN` could well mean `LEFT JOIN` if it wasn't cleared up by the answers here. – martennis Jun 29 '17 at 11:50
4

@Quassnoi Your comment's quoted introductory wiki statement is true of syntactic sugar, but it's inadequate as a definition. Syntactic sugaring is about simpler syntax for special cases of complex syntax. It is more appropriate to say that INNER is a "noise word". – philipxy Jul 08 '19 at 20:46
1

This sounds like saying `long` is syntactic sugar for `signed long int` in C. It doesn't really feel like it's syntactically different because that typedef should just get collapsed into a single node by the lexer, so wouldn't it be the same syntax? IMO `...JOIN t2 USING (c1, c2)` vs `...JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 = t2.c2)` is syntactic sugar. – Nick T Mar 31 '21 at 14:21
@NickT: late to the party, but the `USING` clause can change the query semantics and influence the optimizer decisions. There is no easy substitute for, say, `a FULL JOIN b USING (id) FULL JOIN c USING (id)` without the `USING` clause. Unlike `INNER JOIN` and `JOIN`, which are even lexed to the same thing, `USING` is far beyond being just syntax sugar. – Quassnoi Nov 01 '22 at 02:33

score 216 · Answer 3 · edited Nov 16 '22 at 09:46

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

score 75 · Answer 4 · edited May 19 '18 at 10:57

75

Does it differ between different SQL implementations?

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

edited May 19 '18 at 10:57

Peter Mortensen

30,738
21
105
131

answered May 15 '12 at 13:33

Michał Powaga

22,561
8
51
62

score 59 · Answer 5 · edited May 19 '18 at 10:57

59

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

edited May 19 '18 at 10:57

Peter Mortensen

30,738
21
105
131

answered Feb 19 '09 at 15:09

Kristen

4,227
2
29
36

29

I am the opposite of you: I always say "INNER JOIN" but I never use OUTER; so "LEFT JOIN" and "RIGHT JOIN". Guess I'm just keeping my character counts constant! – Stephen Holt Mar 07 '12 at 10:27

score 41 · Answer 6 · answered Aug 02 '15 at 16:38

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;

Difference between JOIN and INNER JOIN

6 Answers6

Linked

Related