I would like to ask why LEFT INNER JOIN
was deprecated. As far as I know, the join syntax specifies the driving table first (using LEFT
or RIGHT
), followed by the join method (INNER
or OUTER
).
So, why was LEFT INNER JOIN
removed or is no longer recommended?
as far as I know, the join syntax specifies the driving table first
I will allow it is often useful to think this way when building a query. However, the truth is INNER JOIN doesn't really have a "driving table" at all. The results are the same whether one starts with the left side or the right side, and the database will use whichever side it finds more efficient as the starting point. So from a functional standpoint LEFT vs RIGHT accomplishes nothing, and I think most database types have never allowed LEFT vs RIGHT for INNER JOIN at all.
Worse, from a syntactic standpoint allowing it can create confusion. If one uses JOIN
by itself, with no further clarification, we always get an INNER JOIN. But if we say LEFT JOIN or RIGHT JOIN, and omit INNER vs OUTER, the join type flips, and this is now an OUTER join, even though we didn't otherwise specify anything new about that part of the join. This would easily confuse the uninitiated.
It would be especially annoying allowing LEFT INNER JOIN relative to interpreting the (extremely common) LEFT JOIN short-hand. That is, if LEFT/RIGHT are prohibited for INNER JOIN, it's possible to specify any join type with a single additional keyword prepended to JOIN in a way that is completely unambiguos and can't be mistaken for a different JOIN:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN
With each of those, you know exactly what kind of join it is from the just the two keywords, with no possible ambiguity or misinterpretation... but only if LEFT and RIGHT are not permitted for INNER JOIN. And while it's certainly not universal, this "minimum unambiguous text" approach is exactly how most people I know tend to write their JOINs (why add extra noise to the code by including "OUTER" if it adds no new information?).