Joining tables

When creating a query that combines information from multiple tables, you can create joins between the tables. Joins match similar columns between the tables. This allows data from one table to be linked with associated data in another table.

To create and configure a join between two tables in your query:

Identify one column in each table that can be matched against the corresponding column in the other table.
Click and drag the column name from one table and drop it on the column in the other table.

This creates a join between the two tables.

Note: In the resulting SQL statement, the table from which you clicked and dragged the column name is considered the LEFT table, and the table on which you dropped the column name is considered the RIGHT table. This may be significant if your database connection recognizes LEFT OUTER JOIN statements but does not support RIGHT OUTER JOIN statements. See below for more information on LEFT OUTER JOIN and RIGHT OUTER JOIN statements.
To reverse the direction of a join, delete the original join and then re-create it by selecting the column name in the LEFT table and dropping it onto the corresponding column in the RIGHT table.

Drag one of the tables away from the other so that you can distinguish between the ends of the join and the middle portion of the join.
Single-click the join to select it, and then right-click on the join and select Join Properties from the right-click menu.

This displays the Object Properties: Join screen.
Most joins are configured to link data from one table with data from another table when the values in the joined columns are equal. However, if you want to link data based on another type of comparison, you can select a different option from the Join Operator list.

Other options include:
- "<>" (values are not identical)
- "<" (value from left table is less than value on right table)
- "<=" (value from left table is less than or equal to value on right table)
- ">" (value from left table is greater than value on right table)
- ">=" (value from left table is greater than or equal to value on right table)
By default, the join you just created is an INNER JOIN. This means that the resulting SQL statement will return only data from both tables for which a corresponding value exists in both tables. For an example of data rows returned by an INNER JOIN, click here.

However, if you want to return data from one or both tables even when only one of the two tables contains a value, you can choose other options.
- To create a LEFT OUTER JOIN, select the first All from tablename check box and clear the second All from tablename check box. For an example of data rows returned by an LEFT OUTER JOIN, click here.
  
  This returns data from the Orders table even when there is no corresponding data in the Cust table, but not vice versa.
- To create a RIGHT OUTER JOIN, clear the first All from tablename check box and select the second All from tablename check box. For an example of data rows returned by an RIGHT OUTER JOIN, click here.
  
  This returns data from the Cust table even when there is no corresponding data in the Orders table, but not vice versa.
- To create a FULL OUTER JOIN, select both All from tablename check boxes. For an example of data rows returned by an FULL OUTER JOIN, click here.
  
  This returns data from the Orders table even when there is no corresponding data in the Cust table, and it also returns data from the Cust table even when there is no corresponding data in the Orders table.

Note: Some data source drivers do not support all join types. For example, some versions of Pervasive data source drivers support left outer joins but not inner joins, right outer joins, or full outer joins. If your driver does not support the necessary join type, you may need to find a more full-featured driver for the data source.

Click OK to apply these settings to the join.

If you selected one or both of the check boxes to include all rows from either table – creating a LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN – an arrow indicating the direction of the join is displayed.

The example below displays a LEFT OUTER JOIN:

Displaying data types

Adding tables to the query

Setting an alias for the table

Work ing with tables