SQL INNER JOIN

In SQL inner joins are also called simple joins or equijoin. We are now ready to present a SELECT statement with what is called an inner join:

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

Let’s examine this statement line by line. The SELECT keyword on the first line merely states that we want all (*) columns from both tables. The second line, with the FROM clause, indicates that the first table we want to specify is the emp table.

The third line introduces a new INNER JOIN keyword. This keyword is used to specify an additional table that we want to join to. In this case, we want to add in the dept table.

Finally, the fourth line introduces the ON keyword. The ON keyword works in conjunction with the INNER JOIN. The ON specifies exactly how the two tables are to be joined. In this case, we are connecting the deptno column of the emp table (emp.deptno) to the deptno column of the dept table (dept.deptno). Since the deptno column has the same name in both the emp and dept table, we need to specify the table name as a prefix to the deptno column name in the ON clause. The prefix allows us to distinguish between these columns in two separate tables.

The above SELECT produces this data:

Let’s analyze the results.

•   The SELECT * clause specifies the column names to retrieve:

•   The FROM clause specifies the two tables that the database must access:

       • EMP table

       • DEPT table

•   The WHERE clause specifies how the tables are to be joined:

     EMP.DEPTNO = DEPT.DEPTNO

Because the DEPTNO column is common to both tables, it must be prefixed by the table name to avoid ambiguity.

This brings us to this important observation: An inner join only brings back data for which there is a match between both tables being joined.

A nonequijoin is a join condition containing something other than an equality operator. The relationship between the EMP table and the SALGRADES table has an example of a nonequijoin. A relationship between the two tables is that the SAL column in the EMP table must be between the values in the LOSAL and HISAL columns of the SALGRADES table. The relationship is obtained using an operator other than equals (=).

SELECT e.ename, e.sal, s.grade FROM emp e, salgrades s WHERE e.sal BETWEEN s.losal AND s.hisal;

The example of nonequijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.

It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this:

• None of the rows in the salgrade table contain grades that overlap. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salgrade table.

• All of the employees’ salaries lie within the limits provided by the job grade table. That is, no employee earns less than the lowest value contained in the LOSAL column or more than the highest value contained in the HISAL column.

Note: Other conditions, such as <= and >= can be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN.