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?
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.
No, there is no difference, pure syntactic sugar.
INNER falls under this definition. –
Autocade 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. –
Pectase 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. –
Vacillatory 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. –
Autocade 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.)
Does it differ between different SQL implementations?
Yes, Microsoft Access doesn't allow just join. It requires inner join.
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
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
INNERSpecifies 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;
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.
© 2022 - 2024 — McMap. All rights reserved.