Menu
It’s not that important (please take note), but it’s a syntactical particularity and it’s about portability, and I didn’t find a question (or answer) quoting reliable resources or specifications, so I’m curious:
Is it safe to omit the INNER keyword if I explicitly want to INNER JOIN?
By “safe” I mean:
FROM clause needing adjustment.Regarding INNER, MSDN docs (for versions 2005 through 2014) say:
When no join type is specified, this is the default.
The PostgreSQL Documentation implicitly notes:
[ INNER ] JOIN
I didn’t find a corresponding statement in the MySQL Reference Manual nor while searching the web for Oracle or ANSI/ISO SQL, the latter being of particular interest.
466
You can replace the JOIN keyword with a comma in the FROM clause. What do you do next? There's no ON keyword for you to state the joining condition as there would be when using JOIN , e.g., on which two columns you want to join the tables. In this method, you simply use a WHERE clause to do so.
When using join or inner join , the on condition is optional. This is different from the ANSI standard and different from almost any other database.
In databases, LEFT JOIN does exactly that. You can easily notice, that we have 2 more rows, compared to the result of the INNER JOIN query. These are rows for Russia and Spain. Since they both don't have any related city, all city attributes in these two rows have NULL values (are not defined).
We can use 'cross join' without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on.
The INNER/OUTER designations for JOINs are a form of syntactic sugar. A JOIN implies that it is INNER by default. Likewise, LEFT JOIN and RIGHT JOIN imply OUTER by default. This is a SQL universal, as far as I know.
59
Yes, the SQL standard defines the keyword INNER (or OUTER) as optional.
A JOIN is defined like this:
<qualified join> ::=
{ <table reference> | <partitioned join table> }
[ <join type> ] JOIN
{ <table reference> | <partitioned join table> }
<join specification>
As you can see <join type> is optional (because it's in square brackets) and is defined as:
<join type> ::=
INNER
| <outer join type> [ OUTER ]
<outer join type> ::=
LEFT
| RIGHT
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
