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:
-- select query using Where clause or implicit joins SELECT table1.this, table2.that, table3.something, table4.somethingElse FROM table1, table2, table3, table4 WHERE table1.foreignkey = table2.primarykey AND table2.foreignkey = table3.primarykey AND table3.foreignkey = table4.primarykey AND (various other WHERE conditions to check columns in these tables)
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:
-- select query using Inner Join or Explicit Join SELECT table1.this, table2.that, table3.something, table4.somethingElse FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.primarykey INNER JOIN table3 ON table2.foreignkey = table3.primarykey INNER JOIN table4 ON table3.foreignkey = table4.primarykey WHERE (various other WHERE conditions to check columns in these tables)
The later one is hell more readable than the conventional(not now though) WHERE clause written above.
Implicit Join Vs Explicit Join: SQL Execution Plan
Here is an Execution Plan for INNER JOIN vs 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(mostly) and No(sometimes).
Difference Between Implicit and Explicit Join
So here are some other points which make INNER JOIN better than WHERE clause:
- INNER JOIN is ANSI syntax which you should use.
- It is generally considered more readable, especially when you join lots of tables.
- It can also be easily replaced with an OUTER JOIN whenever a need arises.
- The WHERE syntax is more relational model oriented.
- 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