A join is a condition that links the data in separate but related tables. The tables usually have a parent-child relationship. If a query does not contain a join, the database returns a result set that contains all possible combinations of the rows in the query tables.

Cardinality Check

Cardinalities further describe a join between 2 tables by stating how many rows in one table will match rows in another. This is very important for detecting join problems and creating contexts to correct the limitations of a target RDBMS structure.

You should set cardinalities for each join in the schema. The universe design tool can automatically detect and set cardinalities, but you should always manually check the cardinalities, taking into account the nature of the keys that are joined.

Join Operators

You can select an operator for a join from the drop-down list box between the Table1 and Table2 boxes. The operator allows you to define the restriction that the join uses to match data between the joined columns. You can select the following operators for a join:

Type of BO universe joins

You can define the below listed joins in Business Objects universe designer tool.

Equi-Join (Including Complex Equi-Join)

Link tables based on equality (‘=’) between table1 and table 2. Equi-Join is used to join primary key with foreign key. When two tables are joined equally, it returns all the match records from selected tables.
You can also create complex equi-joins, where one join links multiple columns between two tables.

Theta Join

A theta join is a join that links tables based on a relationship other than equality between two columns. A theta join could use any operator other than the “equal” operator. Theta Join basically uses to define the ranges.

To create Theta join in universe designer, please follow the below:

1. Create a join between two tables. An equi-join is created by default.
2. Double click the join.
3. Click a column in the Table1 column list box.
4. Press and hold down the CTRL key and click two columns from the Table2 column list box.
5. Select the cardinality Many (N) to One (1).

Outer Join
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

• Full Outer join: By default you can create either a left outer, or a right outer join depending on which side of the join the outer table is designated. You can also create a full outer join by activating ANSI 92 support for joins in the universe. This is achieved by setting a universe SQL parameter ANSI 92 to YES (File > Parameters > Parameter). This allows the universe to support ANSI 92 syntax for joins, and you can select the tables on either side of a join to be outer tables.

• Left Outer join: Left Outer join returns all the records from left table even when they do match the join condition to another table. For example, (Table1.col (+)=Table2.col1) this join returns all the records from table1.

• Right Outer join: Right outer join returns all the records from right table even when they do match the join condition to another table. For example, (Table1.col = Table2.col1 (+)) this join returns all the records from table2.

Note: Using outer joins can be very useful, but you should be aware of the following performance and limitation on using outer joins in BO.
• Performance can be slower.
• Database limitations on the use of outer joins.
• You should verify how your target RDBMS processes outer joins to avoid incomplete query paths after the original outer join.

Shortcut Join
A shortcut join is a join that provides an alternative path between two tables. Shortcut joins improve the performance of a query by not taking into account intermediate tables, and so shortening a normally longer join path.

A common use of shortcut joins is to link a shared lookup table to another table further along a join path. The join path comprises several different tables in the same context. In such a case, the shortcut join is only effective when the value being looked up has been denormalized to lower levels in a hierarchy of tables, so the same value exists at all the levels being joined.

The shortcut join will be ignored if it is not “short cutting” any join path for a given context. The SQL generated for the related Web Intelligence query will not take into account the ineffective shortcut join.
Shortcut can be defined as mentioned below.

To create a shortcut join:
1. Identify the two tables in a join path that can be linked directly.
2. Create a join between the two tables.
3. Double click the new join. The Edit Join dialog box appears.
4. Select the Shortcut join check box.
5. Select or type other join properties as required.
6. Click OK.

Note: The universe design tool does not consider shortcut joins during automatic loop and context detection. However, if you set the cardinality for a shortcut join you avoid receiving the message ‘Not all cardinalities are set’ when detecting contexts.

Self-Restricting Join

A self-restricting join is not really a join at all, but a self-restriction on a single table. You can use a self- restricting join to restrict the results returned by a table values using a fixed value.

1. Click on Insert menu and click on join to create new join
2. Select the table on which you want to create self-restricting join.
3. Select the same table name from table 2 combo box also.
4. Select the column which should be used for self-restricting join.
5. Edit the expression and put the restriction condition
6. Click OK.

Note: Setting the cardinality for a self- restricting join helps to prevent receiving the message ‘Not all cardinalities are set’ when detecting contexts. You should set cardinality as one-to-one consistently, although the actual setting is not important, as long as it is set.

Please share your thoughts.