Implicit Join v/s Explicit Join in SQL Server

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.

Implicit Join

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.

Explicit Join

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 for Inner/outer Join vs Where Clause
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:

  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 !!!

Advertisements

2 thoughts on “Implicit Join v/s Explicit Join in SQL Server

Say something : I accept all the "Humer&Critic"

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s