Inner Joins

Go Up to InterBase Quick Start: Part IV - Retrieving Data

There are three types of inner joins:

• Equi-joins link rows based on common values or equality relationships in the join columns.
• Joins that link rows based on comparisons other than equality in the join columns. There is not an officially recognized name for these joins, but for the sake of simplicity they can be categorized as comparative joins, or non-equi-joins.
• Reflexive or self-joins compare values within a column of a single table.

To specify a SELECT statement as an inner join, list the tables to join in the FROM clause, and list the columns to compare in the ON clause. Use the WHERE clause to restrict which rows are retrieved. The simplified syntax is:

SELECT <columns> | *
     FROM left_table [INNER] JOIN right_table
     ON left_table.col = right_table.col
      [WHERE <searchcondition>]

There are several things to note about this syntax:

• The INNER keyword is optional because INNER is the default join type. If you want to perform an outer join, you must state the OUTER keyword explicitly.
• The FROM statement often specifies the correlation names

FROM table1 t1 JOIN table2 t2

• The operator in the ON clause does not have to be equality. It can be any of the comparison operators, such as !=, >, >=, or <>.

Performing Inner Joins

1. Enter the following query to list all department managers and their departments where the manager earns more than 80,000. (This is not stated as dollars because some of the employee salaries are in other currencies.)

 SELECT D.department, D.mngr_no  
        FROM Department D INNER JOIN Employee E   
        ON D.mngr_no = E.emp_no  
        WHERE E.salary > 80000  
       ORDER BY D.department

The result set should look like this:

2. The next query uses a subquery to display all departments and department managers where the manager’s salary is at least 20% of a department’s total salary:

 SELECT D.department, D.mngr_no, E.salary  
      FROM Department D JOIN Employee E   
        ON D.mngr_no = E.emp_no 
       WHERE E.salary*5 >= (SELECT SUM(S.salary) FROM Employee S   
           WHERE D.dept_no = S.dept_no)  
     ORDER BY D.department

The subquery sums the salaries for one department at a time and hands out the result to be compared to the manager’s salary (multiplied by 5).

The result set should look like this:

Note: Joins are not limited to two tables. There is theoretically no limit to how many tables can be joined in one statement, although on the practical level of time and resources, 16 is usually considered the workable maximum.

Advance To:

• Outer Joins