Following join types are available in Business Objects Universe Designer which can be used to join two tables.
Equi-join is join which uses “=” equal operator to join two tables. Generally equi join is used to join primary table using primary key with foreign tale using foreign key
When two tables are used using equi-join it returns all those rows from selected table which matches the equality condition.
Outer join links two tables using a join operator. When tables are linked using outer joins the select query returns all the records which matches the join condition and returns all the records from one table even though do not match the join condition.
There are two types of outer join
Left Outer join: This outer join returns all the records from left table even when they do match the join condition.
Right Outer join: This outer join returns all the records from right table even when they do match the join condition.
You should avoid using outer joins as it may cause query to run slower. Outer joins should be placed at the end of a join path otherwise it maybe causes other queries to match a NULL equality condition which might give an error.
A theta join is a between-type join that’s joins tables based on a relationship other than between two columns. It is generally used to demonstrate ranges. A theta join can use any operator other than equality operator.
Lets see how to create a theta join
- From Insert menu click on join to create a new join
- Select the table1 which should be joined to another table using between operator.
- Select the table2
- Now select the two columns from table two which should represent the range.
- Set the cardinality to N-1
- Click Ok
This theta joins uses between operators to join two tables
A shortcut join is a join which provides shorter way to join two tables by avoiding intermediate tables between join paths of tables. It is very helpful to improve the performance of a query as it reduces number of joins in a query.
Shortcut joins are also useful to solve loops in a Universe.
Lets take an example of Shortcut join.
Shop_facts, Article_lookup and product promotion facts are joined through Article id. Now if we want to see Duration and amount sold the query will have un-necessary join of shop_fact and Article_lookup table as there is no join between shop_facts and product_promotion_fact.
However if we join shop_facts and product_promotion_fact, it will create a circular loop which might confuse universe to decide on which join path to take. This can be avoided by using shortcut join instead of using normal join. Shortcut join is represented by dotted line in designer.
Self Restricting Join
Self restricting join is not actually a join but a restriction on a table. Generally it is used to restrict the data returned by a table.
To create a self restricting join
- Click on Insert menu and click on join to create new join
- Select the table on which you want to create self restricting join.
- Select the same table name from table 2 combo box also.
- Select the column which should be used for self-restricting join.
- Edit the expression and put the restriction condition
- Click OK.
Cardinality of self restricting join should be set to 1:1 otherwise it gives error while detecting contexts.
Try to create each type of join in BO universe and practice it and test it using Query Panel.