The Performance of Inner Join Types in SQL

Abstract

The paper compared the performance of four of inner join types; NATURAL JOIN, JOIN … USING, JOIN … ON, and the traditional join or comma join, or WHERE clause join. NATURAL JOIN ; it is used in joining two tables that have columns with the same name, JOIN … USING; is used if several columns share the same name but you don't want to join using all of these common columns. You can determine the common column you want to be used in join in the USING clause, JOIN … ON; is used when join column names are different [1,2]. Every type of these inner join types has its own conditions, but the question is: If there is a query that meet and comply the conditions of all of these types, which of them have better performance or which of them is the fastest?. To answer the question we prepared a simple query in Oracle 10g to join Employees and Jobs tables, the query written in four ways to meet the four types of the inner join in SQL, the first by using NATURAL JOIN, the second by using JOIN … USING, the third by using JOIN … ON, and the forth by using WHERE Clause, each query executed 30 times and the execution time is recorded for each one, and the time average for each query is calculated we found that the average of JOIN …ON is 0.0050s, the average of WHERE Join is 0.0053s, The average of NATURAL JOIN is 0.0077s, the average of JOIN …USING is 0.0083s, we conclude that these types of join can be arranged from fastest to lowest speed as follows: JOIN…ON, WHERE Join, INNER NATURAL JOIN, and JOIN …USING. Keywords-- query; performance; join; inner join; natural join.