CODEWITHSUNDEEP
mysqlsqljoinimplicit
Sierra
Sierra

Reputation: 325

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

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?

Upvotes: 3

Views: 4153

Answers (3)

Jean-François
Jean-François

Reputation: 382

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.

Upvotes: 3

Lukas Eder
Lukas Eder

Reputation: 220902

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.

Upvotes: 0

Sergey Moukavoztchik
Sergey Moukavoztchik

Reputation: 61

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.

Upvotes: 3

Related Questions