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.
