Join types determine how data is retrieved from a database.
To specify a join type:
Select a join line and select View, then Properties or click the Properties icon.
Select a join type and click OK.
Four types of joins are supported:
Simple join (=, >,<, >=, <=+)—A simple (linear) join retrieves the records in both tables that have an identical data in the joined columns.
You can change the default join setting for simple joins by choosing an operator from the drop-down box. The default setting, Equal, is preferred in most situations.
Use ANSI inner join syntax— An inner join is logically the same as a WHERE clause simple join. The INNER join syntax is ANSI92 compliant, specifying the join condition as part of the FROM clause. Like a WHERE clause simple join, the INNER join returns only those rows from the joined tables where the join condition is met. This option is specific to tables in a database and is applied when SQL is generated; it can not be chosen for local joins. It is only available with ODBC connections. The option can not be selected for individual OUTER joins; for those, use settings in the Advanced portion of the OCE wizard. The default behavior is the simple join (WHERE clause syntax). Select this option to optimize a particular query when recommended by your database administrator or database documentation. Example of SQL with this option: SELECT AL1.ACCOUNT_DESC, AL2.ACCOUNT_STATUS_CODE FROM SYSTEM.ACCOUNT AL1 INNER JOIN SYSTEM.ACCOUNTIFO AL2 ON (AL1.ACCOUNT_CODE=AL2.ACCOUNT_CODE) This same statement, using the default join logic, would have been generated as: SELECT AL1.ACCOUNT_DESC, AL2.ACCOUNT_STATUS_CODE FROM SYSTEM.ACCOUNT AL1, SYSTEM.ACCOUNTINFO AL2 WHERE (AL1.ACCOUNT_CODE=AL2.ACCOUNT_CODE)
Left outer join (+=)—A left join retrieves all rows from the topic on the left and any rows from the topic on the right that have matching values in the join column.
Right outer join (=+)—A right join retrieves all rows from the topic on the right and any rows from the topic on the left that have matching values in the join column.
Outer or full outer join (+ = +)—An outer join combines the impact of a left and right join. An outer join retrieves all rows from both tables matching joined column values, if found, or retrieves nulls for non-matching values. Every row represented in both topics is displayed at least once.
A fifth join type, Local Join, is available for use with local Results sets. See Using Local Joins as Filters for more information.
Not all database servers support all join types. If a join type is not available for the database to which you are connected, it is unavailable for selection in the Join Properties dialog box.
Note:
Topics may have moved within the query pane since the join was defined. When topics are moved, the results of the join may not be as expected. Users should confirm that the description of the join (all rows from a, and matching rows from b) is what they intend.