Database Analysis   «Prev  Next»

Create equi-join

Customer Table and Order table

The image below displays how to create an equi-join using records from a Customer table and Order table.
Here are the records created as a result of the equi-join.

equi-join created from Customer table and Order table

Equi-Joins

In its most common form, a join forms new rows when data in the two source tables match. Because we are looking for rows with equal values, this type of join is known as an equi-join (or a natural equi-join ). As an example, consider the two tables in Figure 5.4.1 . Notice that the ID number column is the primary key of the customers table and that the same column is a foreign key in the orders table. The ID number column in orders therefore serves to relate orders to the customers to which they belong.

Figure 5.4.1: Two tables with a primary and foreign key relationship.

Figure 5.4.2: The joined result table.

Assume that you want to see the names of the customers who placed each order. To do so, you must join the two tables, creating combined rows wherever there is a matching ID number. In database terminology, we are joining the two tables over ID number. The result table can be found in Figure 5.4.2. An equi-join can begin with either source table and the result should be the same regardless of the direction in which the join is performed. The join compares each row in one source table with the rows in the second. For each row in the first that matches data in the second source table in the column or columns over which the join is being performed, a new row is placed in the result table.

Equijoins

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE.