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 Fusion for SQL Server 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 Add 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 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 Remove 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]” and Select all rows from “[table]” buttons on the Devart - Query toolbar.

To remove a join, select the connector, then either right-click on 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 on the menu that appears.

To create a new condition, perform the following steps:

  1. Navigate to the group or the join you want to enhance and click the Add 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 on the menu that appears.\

  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 menu that appears 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.

JOINS tab