5.3.7 Creating Relationships Between Objects

You can create relationships between objects by creating a join.

A join identifies a relationship between two or more tables, views, or materialized views.

• About Join Conditions
  A join condition determines how the rows from one object combine with the rows from another object.
• Joining Objects Manually
  You can create a join manually by selecting the Join column in the Design pane.
• Joining Objects Automatically
  Learn how to join objects automatically.

5.3.7.1 About Join Conditions

A join condition determines how the rows from one object combine with the rows from another object.

When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition.

Query Builder supports inner, outer, left, and right joins. An inner join (also called a simple join) returns the rows that satisfy the join condition. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

See Also:

Oracle AI Database SQL Language Reference for information about join conditions

5.3.7.2 Joining Objects Manually

You can create a join manually by selecting the Join column in the Design pane.

To join two objects manually:

1. On the Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. From the Object Selection pane, select the objects you want to join.

   The objects display in the Design pane.

3. Identify the columns you want to join.

   You create a join by selecting the Join column adjacent to the column name. The Join column displays to the right of the datatype, beneath the Remove icon. When your cursor is in the appropriate position, the following tooltip displays:

   Click here to select column for join
   

4. Select the appropriate Join column for the first object.

   When selected, the Join column displays as a dark gray. To deselect a Join column, simply select it again or press ESC.

5. Select the appropriate Join column for the second object.

   Tip:

   You can also join two objects by dragging and dropping. Select a column in the first table and then drag and drop it onto a column in another table.

   When joined, a line connects the two columns.

6. Select the columns to be included in your query. You can view the SQL statement resulting from the join by positioning the cursor over the line.
7. Click Run to execute the query.

   The Results pane displays the query results.

5.3.7.3 Joining Objects Automatically

Learn how to join objects automatically.

When you join objects automatically, the Query Builder suggests logical, existing parent and child relationships between existing columns.

To join objects automatically:

1. On the Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. From the Object Selection pane, select an object.

   The object displays in the Design pane.

3. Click the small icon in the upper left corner of the object. Depending upon the selected object, the icon label displays as Table Actions or View Actions.

   The Actions window appears. Use the Actions window to select all columns within the current object or objects related to the current object.

4. In the Actions window, select the appropriate options:

   • Check All - Select this option to select the first twenty columns in the current object.

   • Add Parent - Displays tables that are referenced as a foreign key to the current object.

   • Add Child - Displays tables that reference the current object in a foreign key.

   If using Add Parent or Add child, the selected object appears and a line connects the foreign key columns.

5. Select additional columns to be included in your query.

   You can view the SQL statement resulting from the join by positioning the cursor over the green line.

6. Click Run to execute the query.

   The Results pane displays the query results.