Defining Relations and Joins

The Relations pane is used to relate similar fields between table, view or stored-procedure objects (see Figure 1.6 for more details). Essentially, objects are drag-dropped from the DB Browser pane into the Relations pane, where they are displayed as lists of fields with check-boxes. Fields between a pair from these objects in the Relations pane may be related via drag-drop method, in this way, defining a relation or a join between them.

Figure 1.6. Showing the Relations pane with four table objects

When a join is defined via drag-drop between two objects in the Relations pane, a join line is drawn with a diamond at the middle. Moreover the proper segment of the command is inserted into the SQL pane in the FROM-clause of the SQL command. In the case, when the related fields have an enforced constraint relation between foreign key and primary key, this line will have respectively the infinity and key icons at the endpoints. Otherwise there will be no icons at the endpoints of the join line.

Figure 1.7. Showing the Join Line Properties dialog

The Join Line Properties dialog may be used for further specifics about the join relation. For example, defining the operator between the related fields, as well as the join type (i.e. INNER, OUTER, LEFT, RIGHT or FULL).

The following are all possible operators between related fields:

            { =, <>, <, <=, >, >= }.

As we have seen in Figure 1.7,  the user may use the right-mouse click on the diamond icon of the join to popup the menu, from which selecting the Properties menu item, the Join Line Properties dialog may be displayed. Observe that if in this dialog the join operator is selected to be other than the equality symbol, then this operator will be shown in the diamond icon. Moreover, the join type is defined via the Include rows section of the dialog, as a result of which the diamond shape is complemented with brackets on the left or the right side.

Figure 1.15. Showing the diamond shape and brackets for join types

For example, see Figure 1.15, which shows the join lines for INNER, LEFT OUTER, and RIGHT OUTER join types.

The join type may be also selected via the Select All Rows from menu items of the popup menu (triggered over the diamond).

Sometimes when the user enters join conditions via the SQL pane directly, there may be situations when this join condition will have an expression instead of a field on one side of the equation (or inequality). In these cases the join line is drawn from a field of one object to the title bar of the other object in the Relations pane (corresponding to the expression), with the diamond icon having the “f*” label. Similarly, if both sides of the join condition equation contain expressions rather than simple fields, then the join line is drawn between the title bars of the objects.

In addition to tables and views that are drag-dropped into the Relations pane from the DB Browser pane, the Relations pane may also contain nested SELECT-statements, that are entered directly as segments of the FROM-clause of the SQL command in the SQL pane. For example the following SQL command entered directly into the SQL pane will result into two objects:

			(SELECT * FROM customers) AS cust
		ON orders.customer_id = cust.customer_id

namely, orders and cust objects, where the field listing of cust object will be the field structure of the customers table. Note that all nested SELECT-statements must have object aliases. Moreover, if object alias is furnished then the corresponding object name in the Relations pane will be this alias, otherwise the actual object’s name is used.

Finally, the Relations pane may be used to select fields from the objects by simply checking the check-boxes corresponding to these field items.