Making Joins Between Tables

A join between two tables describes how these tables are related. Depending on a join kind you can get different results for the same tables. Refer to SQL manual for information on joins and their effect. dbForge Studio allows you to visually create 5 kinds of joins: inner, left outer, right outer, natural, and cross join. You can also easily set very complex conditions for any kind of joins.

Creating Joins

To add a join between two tables, do either of these actions:

  • Use the diagram - Drag-and-drop a source column from one table to a destination column in the second table.

  • Use the Joins tab of the tabbed editor - Click the button of the top tree node. A new empty join with an empty condition will appear. Click the Enter table name field and specify the join tables. You may type them or select them from the drop-down list. Then set the join kind by clicking the red-colored join kind and selecting a required item from the shortcut menu. You can remove a join by clicking its button.

To edit join properties, use the toolbar, connector shortcut menu, or Joins tab of the tabbed editor.

To change a join kind, use the Select all rows from table buttons on the Query toolbar.

To remove a join, select the connector, then either right-click it and choose Remove from Diagram or hit the DELETE key.

Note

Joins are automatically created when a table with foreign key and its referenced table is placed on the diagram.

Working with join conditions and condition groups

The tabbed editor shows joins as a tree with join, condition and group nodes. A join consists of conditions and condition groups. A group consists of several conditions or subgroups, united with a logical operator. You can change a group logical operator by clicking it and selecting another one from the menu. To navigate between conditions and groups, use the UP ARROW and DOWN ARROW keys. Use the LEFT ARROW and RIGHT ARROW keys to navigate between a criteria operator and operands.

To create a new group, click the upper-level group operator or join kind and choose Add Group from the appeared menu.

To create a new condition, do the following:

  1. Navigate to the group or the join you want to enhance and click the button or press the INSERT or PLUS SIGN key. Alternatively, click the upper-level group operator and choose Add Condition on the menu that appears.
  2. Click the condition column and select a column you need from the appeared menu.

  3. Click the condition criteria operator and select a criteria operator you want to apply.

  4. If the condition operator requires operand value(s), specify it (them).

To remove a condition, click the condition button or navigate to the condition and press the DELETE or MINUS SIGN key. To remove a group, click its logical operator and choose Remove Group on the appeared menu or navigate to the group and press the DELETE or the MINUS SIGN key.

You also can copy, cut, and paste conditions and groups with CTRL+C, CTRL+X, and CTRL+V keys.