Joining tables in a query

You have two ways to create joins between tables in a query, automatically or manually. Any joins defined between two tables are represented as arrows connecting the key fields from the two tables.

Joining tables automatically

The Auto Join option on the Query menu enables you to join tables together automatically based upon the default criteria. You can choose to join tables in a query by any of these criteria.

Notes:

Joining tables manually

To create manual joins, take one of the following two ways:

The join relationship is then established between the tables. When more than one relationship is required between two tables, you can create multiple joins between them.

Editing a join

No matter whether a join is created automatically or manually, you can further edit it if required. To do this:

  1. Double-click the join button in the join line to display the Join Options dialog. See the dialog.
  2. Check the Outer Join option to set the join an outer join, then specify the format of the join as required: SQL92 or Use'+'.
  3. Click either the Left, Right or Full radio buttons. Regardless of where the tables are placed in the Query Editor, Left is where the arrow starts and Right is where the arrow points.
  4. Edit the join conditions in the Condition panel according to your requirements.
  5. When done, click OK to accept the changes.

See also Join Options dialog for details about options in the dialog.

Notes:

Alerting when Cartesian product is used

A Cartesian product is used when tables link together with no specifications.

For example, Table A has three values: A, B and C. Table B has three values: 1, 2 and 3. Value A matches value 1, value B matches value 2, and so on. This is a specific match. However, a Cartesian product could have value A matching with 1,2 and 3, and value B matching with 1, 2 and 3, and so on. Depending on the data values, Cartesian products can produce a large and complicated dataset as unnecessary information may be contained.

You can specify whether to alert when this happens as follows:

  1. On the Query menu of the Query Editor, click Current Query Options.
  2. In the Query Option dialog, check or clear Warning when Cartesian Exists.