A join operation lets you retrieve data from two or more tables
based on matching column values. The data in the tables is linked into a single
result. Two joins are needed for this query. The query result needs to include
the RENTALS and CUSTOMERS table entries that have matching CUST_ID column
values. The query result must also include the RENTALS and VIDEOS table entries
that have matching video IDs (VID_ID column values).
- In the Tables pane, right-click the header of the R table,
and then click Create Join on the pop-up menu.
- In the Create Join window under Source,
make the following selections:
- In the Table (alias) list, click RENTALS
(R).
- In the Column list, click CUST_ID.
- Under Target, make the following selections:
- In the Table (alias) list, click CUSTOMERS
(C).
- In the Column list, click CUST_ID.
- Click OK. A join connector
appears between the two columns.
- In the Tables pane, drag your pointer from the VID_ID column
in the R (RENTALS) table to the VID_ID column
in the V (VIDEOS) table.
Look at the SQL Source pane to see the joins in the source code:
FROM
XMLTEST.VIDEOS AS V JOIN XMLTEST.RENTALS AS R JOIN XMLTEST.CUSTOMERS AS C
ON R.CUST_ID = C.CUST_ID ON V.VID_ID = R.VID_ID
In the
Tables pane, both joins have been created as shown here:

You
can change the join type (for example, from the default inner join to a left,
right, or full outer join) in the Tables pane by right-clicking the connector,
clicking Specify Join Type on the pop-up menu, and
then selecting the join type that you want in the Specify Join window.