Setting Join Properties

The algorithm and join type choices in the Join Properties dialog refer to inner and outer tables. WorkSpace Data Federation doesn’t require you to specify which is which when you connect your inputs to a Join operator. Instead, you can choose which of the two connected inputs will be the inner table by using the Inner Table popup.

The terms “inner table,” “inner result set,” and “inner input” are used interchangeably in the discussion of join operations. Similarly, “outer table,” “outer result set,” and “outer input” are equivalent.

Follow these steps to configure the algorithm that WorkSpace uses to perform the join, the type of join, and other options.

Double-click a Join operator. The Join Properties dialog appears.
(Optional) Edit the Name and Description of the operator as desired.

Choose the algorithm that WorkSpace uses to process the join operation. Generally, Sybase recommends the “Automatic” option, which allows Sybase Data Federation to choose an algorithm based on the data with which it is presented at runtime. However, if you know specifics about the size and shape of the two inputs, you can choose an algorithm yourself. The algorithms are as follows:

Algorithm	Description
Automatic	Data Federation automatically determines the most appropriate algorithm based on the size of the incoming result sets. It starts by trying to perform a hash join. If the data in the inner table is too large to fit in memory, Data Federation tries to read the outer table into memory. If the outer table fits, Data Federation performs a hash join with the tables reversed. If neither table fits, Data Federation switches to the sort-merge algorithm.
Sort Merge	A scalable algorithm that works well with data sets of any size. Data Federation breaks the data into chunks in order to sort it before performing the join.
Nested Loop	Processes each row in the inner table once for each row in the outer table. Nested loop is scalable and uses less disk space than the sort-merge algorithm. If one of the tables being joined fits in memory, however, the hash algorithm will generally be more efficient.
Hash	Data Federation reads the inner table into memory and hashes it, allowing very quick lookup as it reads each row of the outer table. This is very efficient if the inner table is small enough to easily fit into memory.

Select a join type. Sybase Data Federation supports inner, outer-left, outer-right, and outer-full join types. These join types work in the same way as their SQL counterparts. They’re summarized here:

Join Type	Description
Inner	Finds the intersection between the two result sets. If no matches appear in the outer input for a given row in the inner input, no output row is produced. If a given inner input matches multiple outer input rows, the data from the inner input is repeated for each mach, combined with each of the outer input’s rows.
Outer Left	If a row in the inner input doesn’t match anything in the outer input, an output row is produced, but with the value NULL for each of the columns that comes from the outer input.
Outer Right	The opposite of outer left join. In an outer right join, if a row in the outer input doesn’t match anything in the inner input, an output row is produced, but with the value NULL for each of the columns that comes from the inner input.
Outer Full	A combination of the outer left join and the outer right join types. If a row from either input result set doesn’t have a matching row in the other result set, it is still included in the output, but with NULL values for the missing columns.

In the Columns table at the bottom of the Join Properties dialog, specify the columns to match in order to join your input result sets.
- Click Add to add a column pair to your join.
- To delete one or more column pairs, select them and click Delete.
- You can edit the specific columns in each pair. (When you add a new column pair, WorkSpace chooses the next available column from the two inputs, which may not necessarily be the columns you want to join on.) Note that if you have already defined columns and you change which input result set is the inner table, WorkSpace swaps your column choices to match.
  - Inner Column:
    Click in field and use the pull-down list to choose a column from the inner table.
  - Outer Column:
    Click in field and use the pull-down list to choose the corresponding column from the outer table.
Click OK to save your changes and close the dialog.