1435

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?

1
  • 3
    per the ANSI SQL 92 specification, they are identical: "3) If a <qualified join> is specified and a <join type> is not specified, then INNER is implicit."
    – DaFi4
    Aug 27, 2020 at 12:01

7 Answers 7

1570

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.

4
  • 23
    Is this true for all data bases (e.g. SQL, postgres?) Does anyone know a link to the documentation explaining this?
    – Chogg
    Oct 25, 2017 at 17:32
  • 22
    It's ANSI SQL standard. See more: contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt; en.wikipedia.org/wiki/SQL-92
    – Indian
    Jan 26, 2018 at 10:14
  • 50
    @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. Feb 11, 2020 at 9: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 <qualified join> is specified and a <join type> is not specified, then INNER is implicit."
    – Olivier
    Mar 9, 2021 at 17:34
356

No, there is no difference, pure syntactic sugar.

7
  • 43
    I wouldn't call this syntactic sugar. "Default" join type, "shorthand," or "alias," maybe.
    – mk12
    Jun 24, 2015 at 13:32
  • 93
    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, 2015 at 13:35
  • 24
    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, 2015 at 14:01
  • 34
    @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, 2017 at 11:50
  • 5
    @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 8, 2019 at 20:46
230

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

78

Does it differ between different SQL implementations?

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

61

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
3
  • 30
    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! Mar 7, 2012 at 10:27
  • you are going to have to explain this one. How is "inner" and "outer" both optional keywords when leaving it off of both of them you end up with "join"?
    – John Lord
    Jan 23 at 21:20
  • 1
    @John Lord I use "LEFT OUTER JOIN" and "JOIN", never "INNER JOIN"
    – Kristen
    Jan 25 at 7:50
43

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;

enter image description here

-2

INNER JOIN is the same as JOIN. There is no performance benefit or output difference. Both give an intersection when used with an equivalent condition (like one column of a table equals one column of the second table) that is written in 'ON'.

This can be achieved according to old syntax also without using the word join altogether as shown below.

SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.COLUMN1=TABLE2.COLUMN4;

(Using random numbers for columns)

All the above three methods give exactly the same result.

1
  • 4
    This adds nothing to the many answers on this very old very voted Q&A. Moreover like most of the answers there is way more words than needed since the answer is just "No".
    – philipxy
    Sep 27, 2023 at 14:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.