Creating Join Views¶

A join view executes the relational algebra operation JOIN on a series of input views. These views can belong to different databases.

To create a join view, click Join on the File > New menu or, right-click the Server Explorer and click Join on the menu New.

The Tool will open the “Join view” dialog and it will add the view that is currently selected in the Server Explorer. To add more views to the join view, click the Model tab and drag the views from the Server Explorer to this tab.

You can join more than two views in the same join view.

In our example, we will join the view incidents created in the previous section and the Web service view average_monthly_sales, created in the section Creating Base Views from a SOAP Web Service.

The “Join view” dialog has six tabs:

1. Model: drag the views that will form the join. Consider this:

   • When you add two views to this tab, the Administration Tool will automatically create a join condition if there is an association between these views and this association is a referential constraint (i.e. it is a primary key/foreign key relationship). The join condition will be the same as the condition mappings of the association.

   • When adding a view to a join view, the Administration Tool now suggests the views you may want to add to the join view, based on the associations of that view. The suggestions are next to the label Associated views; just click the view to add it automatically.

   To define a simple join condition do this:

   • If you use the Administration Tool, drag the field of one view to the field of the other view. To configure the operator used in this JOIN condition, double-click the arrow that joins both fields and select the operator. To add more complex join conditions, go to the “Join Conditions” tab.

   • If you use the Design Studio, first, click on the link button (on the top-right side of the source view) and drag it to the target view. After connecting the views, click the link and a dialog will show up at the bottom of the Model tab with the fields of both views. In this dialog, link a field of the left view with another of the right view. The source view and the target view are the left view of the join and the right view of the join respectively.

   

   Connecting internet_inc with phone_inc using the Design Studio¶

   To define a cross join, just drag the views into the dialog and do not link any field.

   In the “Model” tab of the view, you can add “View parameters”. The section Parameters of Derived Views explains what are view parameters and the rules you need to follow when using them.

2. Join Conditions: tab where you can define the join strategy and the join conditions:

   1. Configure the join strategy. To do this, click on the icon located between the names involved in the join. The options are:

      1. Join type: Inner, Left outer, Right outer and Full.

      2. Execution method: Hash, Merge, Nested and Nested parallel. If you select nested parallel, you have to provide the maximum number of parallel subqueries (“Nested parallel number”). If you select the option “Any” method, the Server will try to select the best suitable join method.

      3. Order in which the input views are considered. If it is “Ordered”, the view on the left of the join will be considered the first view.

      4. If you want to define a join condition between a field of a simple type (int, long, etc.) and a subfield of a compound type, the subfield of the compound type has to be placed in the left side of the join condition.

         For example, let us say that you want to create a join view with these two views:

         1. view_a, which has a field f1 of type int

         2. view_b, which has a field f2 of type array. This array has two int subfields: f2_a and f2_b.

         If you want the join condition to be f1 = f2_b, you have to define the join condition as ... JOIN ... ON (f2).f2_b = f1 That is, the subfield of the array must be placed on the left side of the join condition.

      Note

      These preferences may have a significant impact on the performance of queries. Read the section Optimizing Join Operations for more information about this.

   2. Edit the join conditions added in the “Model” tab, by clicking on them.

      Click Add new condition to define a new join condition and Add cross join to define a cross join between two views.

      In simple scenarios, you can define join conditions and cross joins graphically from the “Model” tab. However, in complex situations you need to define these from the “Join Conditions tab”:

      1. By default, the conditions added with the “New Join Condition” dialog are added to the most internal join that contains all the fields of the condition.

         In complex joins, you may need to specify to which join the new condition belongs. To do that, from the “New Join Condition” dialog, select the check box at the bottom and select the views involved in the target join.

      2. From the “Model” tab, you cannot specify a join between a view and the result of a cross join of two views. E.g.:

         (view_a CROSS JOIN view_b) INNER JOIN view_c ON view_a.field1 = view_c.fieldA
         

         Note

         In the Design Studio, to define a join between a view and the result of a cross join of two views, follow these steps:

         1. Connect the views view_a and view_b (do not add join conditions)

         2. Connect view_a and view_c.

         3. Add the condition by connecting the field view_a.field1 with the field iew_c.fieldA.

         The Add cross join button is not available in the Design Studio.

         To create this view, follow these steps:

         1. Drag the three views into the “Model” tab.

         2. In the “Join Conditions” tab, click Add cross join, select view_a and view_b and click Ok.

         3. Click Add join condition and enter the condition view_a.field1 = view_c.fieldA. Then, select the check box at the bottom of the dialog. In the Left subview list, select (view_a CROSS JOIN view_b) and in Right subview, view_c.

   The section Join Conditions with Similarity Operators explains the meaning of the operator ~.

   Note

   To create Cross Join views, you just have to drag the views into the “Model” tab and do not define any join condition in the “Model” tab, the “Join” tab or the “Where condition” tab.

   Note

   Do not use fields that are “View parameters” in the join conditions.

3. Where Conditions: tab that allows you to add WHERE conditions to the definition of the view.

4. Group By: tab that allows you to add GROUP BY attributes to the view.

5. Output: tab that allows you to configure the output of the view. That is, renaming the view and its fields, add derived attributes, define the primary key of the view, etc.

6. Metadata: tab that allows you to define the folder where the new view will be stored and provide a description for the new view.

The tabs Where Conditions, Group By, Output and Metadata work in the same way as in the Union view dialog. The section Creating Union Views explains in more detail how to use them.

Constructing the incidents and average_monthly_sales join view (“Model” tab)¶

In our example:

• Click on Join on the menu File > New.

• Drag the views incidents and average_monthly_sales to the “Model” tab.

• Add the join condition incidents.taxId=average_monthly_sales.taxId by dragging the name of the field taxId of one of the tables, to the field taxId of the other table.

• In the “Output” tab:

  • Rename the view to incidents_sales.

  • Remove the field incidents.taxId. To do this, select this field and click on Remove selected.

After this, click Save () to create the view. Then, the Tool will display the schema of the new view.

Schema of the incidents_sales join view¶