What Are Join Types?

This article will explore the different types of joins that can be used for accessing database tables, and will explain how to implement these joins in the InetSoft application.

Contents

What is a Join Type?
Inner Join
Outer Join
Inequality Join
Cross Join
Creating a Join in InetSoft

What Is a Join Type?

In database theory, a join is a method of combining data from multiple tables while enforcing a constraint on the data. The nature of the constraint determines the type of join. The following sections explore the various join types and join constraints. The join of two database tables, A and B, can be thought of theoretically as comprising two steps:

1. Form the Cartesian product (or Cross Join) of table A and table B, keeping all columns from both tables. The cross join is the combination of every row in Table A with every row in Table B, which results in a new table that has size length(A)*length(B).


2. Remove every row that does not meet the specified join constraint.

Note that from an implementation perspective, performing the above steps (in particular, forming the cross join) is not computationally efficient, and database software uses more efficient algorithms to do this. However, the steps above are useful for conveying the theoretical basis of joins, and for facilitating explanation of join types.

Inner Join

The Inner Join is the most common join type and is based on an equality constraint. Rows that are retained in the result table are rows where the equality constraints are satisfied. For example, if the equality constraint is A4=B2, meaning that the value of column 4 in Table A must match the value of column 2 in Table B, then the only rows retained in the result set of the cross-joined table are those where the value in column 4 of Table A matches the value in column 2 of Table B.

For example, consider the following two tables:

The inner join (equality join) between these two tables would use the corresponding REGION_ID columns as the join columns, enforcing the equality constraint SALES_EMPLOYEES(REGION_ID) = REGIONS(REGION_ID). To follow through with the two theoretical steps described earlier, this would first mean forming the cross-product of the two tables, and then eliminating the rows that fail to satisfy the join condition. (The rows to eliminate are circled below, since the regions in these rows are not equal).

The resulting inner join preserves just the rows that satisfy the join condition, and the result set is shown below. (Typically, the duplicate join column can then be hidden, as it is entirely redundant.)

The same procedure can be followed to understand multiple inner joins (more than two join columns, and two or more tables): First form the cross-product of the tables, and then remove all rows that fail to satisfy the specified multiple join conditions.

One of the limitations of the inner join is that because only strict matches are retained in the result set, if records in one table have NO match in the other table, the records are lost from the result set. In many cases this is desirable; for example, if no salespeople have sales in region USA West, it might be appropriate to drop USA West from the results. In other cases, we must also retain in the result set records that have no match. This can be accomplished by using an Outer Join.

Outer Join

An Outer Join is the same as an inner join, but preserves records from one or both tables even when the values in that table's join column have no match in the other table's join column. This would be useful, for example, in the case of a table that contains Order information and a second table that contains Return information. When you join these two tables together to obtain a new table of Orders with Returns, you may not want to lose all the "unreturned" order information, as would happen with an inner join. An outer join can preserve that information.

To see how the outer join works, consider the two tables below. Note that each table contains some data (indicated by arrows) that does not have a match in the join column of the other table.

>

Now we can choose to keep all of the data from the SALES_EMPLOYEES table, yielding a left outer join. Note that the record with no region match (i.e., region 3, "Fred") is still retained in the table.

Alternatively, we can choose to keep all of the values from the REGIONS table, yielding a right outer join. Note that the record with no region match (i.e., region 4, "Middle USA") is still retained in the table.

We can also choose to retain values from BOTH tables that have no match in the other table. This yields the full outer join:

All of these outer join types are valuable for particular applications.

Inequality Join

The Inequality Join replaces the equality constraint of the inner join with an inequality constraint (greater than, less than, not equal to, etc.). The procedure remains the same: First form the cross join, and then retain rows that match the inequality join condition.

For example, here is the "not equal" join of the original SALES_EMPLOYEES and REGIONS tables:

These are the records in the cross join where the region IDs do not match. Note that these are exactly the records that were excluded from the inner (equality join). The other inequality joins (greater than, etc.) can be understood in the same way: Simply enforce the join condition on the cross joined table.

Cross Join

The Cross Join or Cartesian product of two tables was already mentioned earlier to help explain join theory. It is rarely used in practice because of the computational load in implementing this join, and relative lack of practical applications. There are nevertheless some special cases when such joins may be required.

Creating a Join in InetSoft

Creating any of these join types is extremely easy in the InetSoft Visual Composer. To create a join, select the two tables that you want to join, and press the Join button in the toolbar. Select the desired join type.

For Inner/Outer Join, this opens the Join Editor. (For Cross Join and Merge Join, the result is created immediately because no relationships need to be defined.) In the Join Editor, drag a column from one table to the other table to specify the join columns.

Click on the join operator and select the desired join constraint (inner, outer, or inequality).

Press Done to close the Join Editor. This creates the desired joined table.

The new joined table can be used in additional joins or other data operations such as grouping and aggregation, filtering, sorting, and so on. If the joined result is the final result, it can be used directly to create dashboards and reports in InetSoft.