Skip to main content

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 !!!
Labels:
Location: Bengaluru, Karnataka, India

Comments

Post a Comment

Popular posts from this blog

Fake CVR Generator Denmark

What Is Danish CVR The Central Business Register (CVR) is the central register of the state with information on all Danish companies. Since 1999, the Central Business Register has been the authoritative register for current and historical basic data on all registered companies in Denmark. Data comes from the companies' own registrations on Virk Report. There is also information on associations and public authorities in the CVR. As of 2018, CVR also contains information on Greenlandic companies, associations and authorities. In CVR at Virk you can do single lookups, filtered searches, create extracts and subscriptions, and retrieve a wide range of company documents and transcripts. Generate Danish CVR For Test (Fake) Click the button below to generate the valid CVR number for Denmark. You can click multiple times to generate several numbers. These numbers can be used to Test your sofware application that uses CVR, or Testing CVR APIs that Danish Govt provide. Generate
Post a Comment
Read more

How To Iterate Dictionary Object

Dictionary is a object that can store values in Key-Value pair. its just like a list, the only difference is: List can be iterate using index(0-n) but not the Dictionary . Generally when we try to iterate the dictionary we get below error: " Collection was modified; enumeration operation may not execute. " So How to parse a dictionary and modify its values?? To iterate dictionary we must loop through it's keys or key - value pair. Using keys
Post a Comment
Read more

How To Append Data to HTML5 localStorage or sessionStorage?

The localStorage property allows you to access a local Storage object. localStorage is similar to sessionStorage. The only difference is that, while data stored in localStorage has no expiration time untill unless user deletes his cache, data stored in sessionStorage gets cleared when the originating window or tab get closed. These are new HTML5 objects and provide these methods to deal with it: The following snippet accesses the current domain's local Storage object and adds a data item to it using Storage.setItem() . localStorage.setItem('myFav', 'Taylor Swift'); or you can use the keyname directly as : localStorage.myFav = 'Taylor Swift'; To grab the value set in localStorage or sessionStorage, we can use localStorage.getItem("myFav"); or localStorage.myFav There's no append function for localStorage or sessionStorage objects. It's not hard to write one though.The simplest solution goes here: But we can kee
Post a Comment
Read more