Difference Between Implicit and Explicit Join

If you are looking for the performance difference between INNER JOIN(Explicit Join) and WHERE clause(Implicit Join), Let me clear the SQL Engine(either its mySql/SQL Server or Oracle) got more and more intelligent during the time. So there is no such difference in performance whatever you write from INNER JOIN or WHERE clause.


Let me write a simple query using WHERE clause or Implicit Join:



This SQL Query is written with only 4 tables, 5-10 tables in queries is common scenario while you are working with a good size of project. And believe me or not, writing these queries with WHERE clause is definitely going to get your head off the shoulders.

Now let me write it using INNER JOIN or Explicit Join:



The later one is hell more readable than the conventional(not now though) WHERE clause written above.

Here is an Execution Plan for INNER JOIN and WHERE clause, which is same for both the cases:

Execution Plan Comparison: Implicit Join vs Explicit Join

Execution plan credit: Blog do Ezequiel

So Is the only difference READABLITY??

Answer is Yes and No.


So here are some other points which make INNER JOIN better than WHERE clause:

1. INNER JOIN is ANSI syntax which you should use.
2. It is generally considered more readable, especially when you join lots of tables.
3. It can also be easily replaced with an OUTER JOIN whenever a need arises.
4. The WHERE syntax is more relational model oriented.
5. A result of two tables JOIN'ed is a Cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

And some of interviewers who believe in playing word tricks(I hate them really) this post is actually Difference: Implicit Joins vs Explicit Joins

Cheers !!!