Both these joins will give me the same results:
SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FKvs
SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FKIs there any difference between the statements in performance or otherwise?
Does it differ between different SQL implementations?
asked Feb 19, 2009 at 14:47
2
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.
Sled
18.1k27 gold badges118 silver badges160 bronze badges
answered Feb 19, 2009 at 14:50
palehorsepalehorse
25.3k3 gold badges38 silver badges47 bronze badges
4
No, there is no difference, pure syntactic sugar.
answered Feb 19, 2009 at 14:48
QuassnoiQuassnoi
404k89 gold badges606 silver badges610 bronze badges
7
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.)
philipxy
14.6k6 gold badges34 silver badges80 bronze badges
answered Feb 28, 2012 at 8:23
net_prognet_prog
9,68316 gold badges54 silver badges69 bronze badges
Does it differ between different SQL implementations?
Yes, Microsoft Access doesn't allow just join. It requires inner join.
answered May 15, 2012 at 13:33
Michał PowagaMichał Powaga
22k8 gold badges51 silver badges62 bronze badges
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
answered Feb 19, 2009 at 15:09
KristenKristen
4,1812 gold badges28 silver badges35 bronze badges
2
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> ] ] JOINShowing 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;answered Aug 2, 2015 at 16:38
Martin SmithMartin Smith
427k87 gold badges720 silver badges816 bronze badges