5.2.5 Changing the Join Type

The join type can be changed for all joined tables except for Merge-joins and Cross-joins. (Merge joins and cross joins do not define a relationship between tables, so there is no relationship that can be changed.)

To change the join type of a joined table, follow these steps:

1. If the joined table is in Default view, switch the table to Composition view. (Press the 'Change View' button in the table title bar, and select 'Composition' from the menu. See Editing a Composition Table for more details.)

2. Open the 'Join Types' dialog box. You can do this in any one of the following ways:

a. Click the join connector link to select it. The selected link is highlighted in blue. Right-click on the highlighted join connector link and select 'Join Properties' from the context menu. This opens up the 'Join Types' dialog box for the selected join.

 

b. Right-click on the title bar of the Data Block and selected 'Join Properties'. This opens up the 'Join Types' dialog box for all joins.

c. Press the 'Join' symbol next to the Data Block name in the Worksheet Explorer. (See Navigating a Data Worksheet for more information about the Worksheet Explorer.) This opens up the 'Join Types' dialog box for all joins.

 

3. In the 'Join Types' dialog box, select the join columns from the drop-down menus for each table.

4. Select the join condition operator from the middle menu. The '=' operator produces the “equi-join.” The inequality operators produce their respective “non-equijoins.”

5. (Optional) To convert an equi-join to an outer join, select 'include all values' for the top or bottom table, or for both tables.

These selections correspond, respectively, to the left outer join, the right outer join, and the full outer join. In addition to the matching rows preserved by the equi-join, the left outer join preserves all the rows in the top table, while the right outer join preserves all the rows in the bottom table. The full outer join preserves all the rows of both tables.

 

When an outer join is specified, the additional rows included from a table (beyond those selected by the equi-join) do not have matching rows in the other table. Therefore, outer-join tables generally exhibit empty cells corresponding to the unmatched rows.

Example: Changing the Join Type  >>

Follow the steps below to change the join type of a joined table from inner join to left outer join.

1. Create a new Worksheet by clicking on the 'New Worksheet' button.

2. Expand the 'Data Source' node, and the 'Orders' node.

3. Drag the 'customers' node from the tree to an empty cell in the Worksheet. A new table named 'customers1' is created.

4. Expand the 'DWS' node on the 'Orders' tree.

5. Drag the 'NY Customers' node from the tree to an empty cell in the Worksheet. A new table named 'NY Customers1' is created.

6. Select the 'customer_id' column header in table 'customers1', hold down the left mouse button and drag it from the 'customers1' table onto the 'CUSTOMER_ID' column header of the 'NY Customers1' table. This creates a new (inner) joined table called 'Query1'.

 

7. Right-click on the title row of the 'Query1' table and select 'Preview' from the context menu.

 

Notice that only the customers from the state of NY are listed.

8. Close the preview.

9. In the Data Worksheet, click the join connector in table 'Query1' to select it.

10. Right-click the connector and select 'Join Properties' from the context menu. This opens the 'Join Types' dialog box.

 

11. Select the 'include all values' option for the 'customers1' table and click 'OK'.

 

12. Preview the table again. Notice that the two tables are now joined using the left outer join.

 

 

<< 5.2.4 Merge Join © 1996-2013 InetSoft Technology Corporation (v11.5) 5.3 Merging Tables >>