InterBase Quick Start: Part IV - Joining Tables

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

Joins enable a SELECT statement to retrieve data from two or more tables in a database. You list the tables in the FROM clause. The optional ON clause can reduce the number of rows in the result, and the WHERE clause can further reduce the number of rows.

From the information that describe a join in a SELECT statement, InterBase builds a table that contains the results of the join operation (also called a dynamic or virtual table).

InterBase supports two types of joins: inner joins and outer joins.

Correlation Names

If you query multiple tables, it is important to unambiguously identify columns. The syntax that allows you to do that is the table name followed by a period and the column name:

table_name.col_name

In complex queries specifying column names in that way can get tedious, so InterBase permits you to define a shorter version of the table name in the FROM clause of a join. This short name is called a correlation name or an alias. You see many examples of correlation names throughout this tutorial. The syntax is as follows:

SELECT a.col,
       b.col
FROM   table_1 a,
       table_2 b
on     a.some_col = b.some_col
WHERE  a.conditional_col <condition> ...

In the FROM clause, the correlation name of table_1 is a and the correlation name of table_2 is b.

Note:
If you include a subquery in a join, you must assign new correlation names to any tables that appear in the main query.

Inner Joins

Inner joins link rows in tables based on specified join conditions and return only those rows that match the join conditions. If a joined column contains a NULL value for a given row, that row is not included in the results. Inner joins are the more common type because they restrict the returned data and show a clear relationship between two or more tables.

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 no 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 syntax for an INNER join is:

SELECT col_name [, col_name, ...] | *
FROM   left_table [inner]
join   right_table
ON     left_table.col = right_table.col 
[WHERE  <search_condition>]

Keep in mind:

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.
Usually you specify correlation names FROM statement:

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 <>.

Using Inner Joins

Enter the following query to list all department managers and their departments where the manager earns more than 80,000.

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 looks like this:

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

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 of a department compares the result to the salary (multiplied by 5) of the manager of that department.

The result set looks like this:

Outer Joins

Outer joins link rows in tables based on specified join conditions but return rows whether they match the join conditions or not. Outer joins are useful for viewing joined rows in the context of rows that do not meet the join conditions.

There are three types of outer joins:

Left outer join: Retrieves all rows from the left table in a join, and retrieves any rows from the right table that match the search condition that you specify in the ON clause.
Right outer join: Retrieves all rows from the right table in a join, and retrieves any rows from the left table that match the search condition that you specify in the ON clause.
Full outer join: Retrieves all rows from both the left and right tables in a join regardless of the search condition that you specify in the ON clause.

Outer join syntax is very similar to that of inner join. The operator in the ON clause does not have to be equality. It can be any of the comparison operators, such as !=, >, >=, or <>.

SELECT     col_name [, col_name, ...] | *
FROM       left_table {left | right | full}
outer join right_table
ON         join_condition 
[WHERE      <search_condition>]

Using Outer Joins

The following outer join retrieves employee names from the Employee table and project IDs from the Employee_project table.
```
SELECT e.full_name,
       p.proj_id
FROM   Employee e
       left outer join Employee_project p
                    ON e.emp_no = p.emp_no
ORDER  BY p.proj_id
```
Because it is an outer join, it also retrieves the employee names that do not have a corresponding project ID. Those rows display a <null> value instead. The image below shows a part of the results:

Now reverse the order of the tables and execute the query again.

SELECT e.full_name,
       p.proj_id
FROM   Employee_project p
       left outer join Employee e
                    ON e.emp_no = p.emp_no
ORDER  BY p.proj_id

This produces a different result. Since there are no projects that do not have a corresponding employee, you do not see any <null> values.

As a last exercise with joins, repeat the previous query but change the join from left to right. Before you execute the query, take a moment and try to guess what the result is going to be.
```
SELECT e.full_name,
       p.proj_id
FROM   Employee_project p
       right outer join Employee e
                     ON e.emp_no = p.emp_no
ORDER  BY p.proj_id
```
You can see that the result is the same as in step 1. That confirms that a right outer join on tables B JOIN A is the same as a left outer join on tables A JOIN B.

Advance To:

Formatting Data