Using the Advanced Join Dialog
The Advanced Join feature of the Query Tool allows you to perform more complex join
operations rather than the common INNER JOIN relationship. It also permits you to create,
change, add or delete a join relationship. To realize these functions, launch the Join
Dialog by either
- Pressing the Advanced Joins button on the tool bar, or
- Right-clicking on a relationship link in the Query tab to invoke a pop-up menu, and then
select Advanced Joins.

Here is the Join Dialog:

This dialog comprises a left and right panel. The left panel displays the currently
selected tables in the query. Those tables that have a relationship are shown joined
together. The right panel shows the tables that have been selected from the left panel
together with the relationship type between their joined columns. Under the Join type,
there are options to choose which type of join you wish to perform, and below is the Join
Relationships section where you can edit, insert or delete a join relationship as you
wish.
Here are the tasks you can perform on the Join Dialog:
Performing an Advanced Join Operation
Here are the available join operations:
INNER JOIN
This is the most common join type, and is the default relationship provided when you
create a join. This relationship will include only those matching records from both
tables, that is, those records having a common field where their values correspond. To
achieve this:
- Selecting the first option under Join Type.

- Press the OK button to establish the relationship.
LEFT JOIN
This relationship will include all records from the table on the left as well as only
those matching records from the table on the right. To achieve this:
- Select the second option under Join Type.

- Press the OK button to establish the relationship.
RIGHT JOIN
This relationship will include only those matching records from the table on the left
as well as all records from the table on the right. To achieve this:
- Select the third option under Join Type.

- Press the OK button to establish the relationship.
FULL JOIN
This relationship will include all records from both tables. To achieve this:
- Select the fourth option under Join Type.

- Press the OK button to establish the relationship.
CROSS JOIN
This relationship has no connection between the records from both tables. Therefore,
each record from the table on the left will be associated with all records from the table
on the right, thereby creating a one-to-many relationship, or a 'cross-product' between
the tables. To achieve this:
- Click on the correlation text under Join Relationships.
- Press the Delete button to remove the association.

- Repeat steps 1 and 2 if there are multiple correlations between the two tables, until
there is absolutely no relationship between them.
- Press the OK button to establish the deletion.
Creating a Join Relationship
You can also use the Edit Join Dialog to create a join relationship between tables if
there is none existing. To achieve this, follow these steps:
- Launch the Join Dialog.
- Click on the second table that you intend to join to the first. Ensure that both tables'
names appear on the right panel.
- Click on the Insert button under Join Relationship to instigate the Expression Builder Dialog.
- Proceed to type the relationship you wish to build, by indicating the columns to join
from both tables. For example, "EMPINFO.IDNUM" = "SALARY.IDNUM".
- Click the OK button to save this expression. Your new relationship now
appears in the text area.
- Press the OK button on the Join Dialog to establish the join
relationship.
Editing a Join Relationship
To edit an existing relationship, follow these steps:
- Launch the Join Dialog.
- Click on either table that holds the relationship you wish to edit.
- Click on the correlation text under Join Relationships.
- Press the Edit button to instigate the Expression
Builder Dialog.
- Proceed to change the relationship.
- Click the OK button to save this expression. You can now see your
change in the text area.
- Press the OK button on the Join Dialog to validate the change.
Inserting a Join Relationship
To insert a relationship, follow these steps:
- Launch the dialog.
- Click on the set of tables you wish to add your relationship to.
- Press the Insert button to instigate the Expression
Builder Dialog.
- Proceed to type your new relationship, specifying the columns to join.
- Click the OK button to save this expression. The new added relationship
now appears in the text area.
- Press the OK button on the Join Dialog to establish the relationship.
Deleting a Join Relationship
To delete a relationship, follow these steps:
- Launch the dialog.
- Click on the set of tables possessing the relationship.
- On the text area under Join Relationships, click on the relationship you wish to remove.
- Click the Delete button. That relationship is now removed from the text
area.
- Press the OK button to save the deletion.