Difference between JOIN and INNER JOIN
Solution 1
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
No, there is no difference, pure syntactic sugar.
Solution 3
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
Does it differ between different SQL implementations?
Yes, Microsoft Access doesn't allow just join
. It requires inner join
.
Solution 5
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
driis
.NET Developer, Software architect, general geek. You can follow on Twitter, or read my blog, if you like.
Updated on July 08, 2022Comments
-
driis almost 2 years
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?
-
Stephen Holt about 12 yearsI 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!
-
Karl Kieninger about 9 years@Jonathan.There is no concept of direction on an inner join. Outer joins can produce unmatched results sets and those can vary based on direction. Inner require matching so the direction does not matter.
-
mk12 almost 9 yearsI wouldn't call this syntactic sugar. "Default" join type, "shorthand," or "alias," maybe.
-
Quassnoi almost 9 yearsIn 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. -
mk12 almost 9 yearsIf 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.
-
martennis almost 7 years@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 meanLEFT JOIN
if it wasn't cleared up by the answers here. -
Chogg over 6 yearsIs this true for all data bases (e.g. SQL, postgres?) Does anyone know a link to the documentation explaining this?
-
Indian over 6 yearsIt's ANSI SQL standard. See more: contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt; en.wikipedia.org/wiki/SQL-92
-
philipxy almost 5 years@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".
-
Tuukka Haapaniemi over 4 years@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.
-
Olivier about 3 yearsThanks @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."
-
Nick T about 3 yearsThis sounds like saying
long
is syntactic sugar forsigned 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.