What's the purpose of an IMPLICIT JOIN in SQL?

Question

So, I don't really understand the purpose of using an implicit join in SQL. In my opinion, it makes a join more difficult to spot in the code, and I'm wondering this:

Is there a greater purpose for actually wanting to do this besides the simplicity of it?

Jermyn

Does this answer your question? Explicit vs implicit SQL joins – Grooved

Answer 1

Fundamentally there is no difference between the implicit join and the explicit JOIN .. ON ... Execution plans are the same.

I prefer the explicit notation as it makes it easier to read and debug. Moreover, in the explicit notation you define the relationship between the tables in the ON clause and the search condition in the WHERE clause.

Technetium

I agree with you about the explicit being easier to read. I'm a bit disappointed that it has no defined purpose that is separate from the Explicit Join. In any case, thank you for the response. :) – Jermyn

Do you think perhaps that there is a timeline to explicit and implicit? Perhaps it was at some turn that explicit became more popular. I tried to find some sources on this, maybe someone knows, but did one come after the other? – Jermyn

You may find this article interesting as it somewhat responds to your question. sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Alanis

Answer 2

Explicit vs implicit SQL joins

When you join several tables no matter how the join condition written, anyway optimizer will choose execution plan it consider the best. As for me: 1) Implicit join syntax is more concise. 2) It easier to generate it automatically, or produce using other SQL script. So I use it sometimes.

Pettitoes

Is that so? I was reading a short work on the implicit join, and it appears to be...well, not recommended. What do you think makes it more concise? – Jermyn

It's notation shorter than explicit join. =) Anyway in production code I'm usually using explicit style. Because its better readability. Except the cases when SQL is need to be generated automatically using other query or script. It easier to put all table names under "FROM" and join conditions under "WHERE", and that's it. – Pettitoes

Answer 3

Others have answered the question from the perspective of what most people understand by "implicit JOIN", an INNER JOIN that arises from table lists with join predicates in the WHERE clause. However, I think it's worth mentioning also the concept of an "implicit JOIN" as some ORM query languages understand it, such as Hibernate's HQL or jOOQ or Doctrine and probably others. In those cases, the join is expessed as a path expression anywhere in the query, such as e.g.

SELECT 
  b.author.first_name, 
  b.author.last_name, 
  b.title, 
  b.language.cd AS language
FROM book b;

Where the path b.author implicitly joins the AUTHOR table to the BOOK table using the foreign key between the two tables. Your question still holds for this type of "implicit join" as well, and the answer is the same, some users may find this syntax more convenient than the explicit one. There is no other advantage to it.

Disclaimer: I work for the company behind jOOQ.

Incarcerate