Creating Outer Joins

See Also

By default, the Query Designer creates an inner join between tables. If you want to include data rows in the result set that do not have a match in the joined table, you can create an outer join.

When you create an outer join, the order in which tables appear in the SQL statement is significant. The first table you add becomes the "left" table and the second table becomes the "right" table. (The actual order in which the tables appear in the Diagram pane is not significant.) When you specify a left or right outer join, you are referring to the order in which the tables were added to the query and to the order in which they appear in the SQL statement in the SQL pane.

To create an outer join

1. Create the join, either automatically or manually. For details, see Joining Tables Automatically or Joining Tables Manually.
   
   
2. Select the join line in the Diagram pane, and then from either the Query menu or the right-click menu, choose Select All Rows from table, selecting the command that includes the table whose extra rows you want to include.

   –or–

   In the Diagram pane, right-click the join line you want to change to an outer join, and then choose Properties from the shortcut menu. Under Include rows in the Join Line tab of the Property Pages dialog box, choose the option specifying the table from which you want to include all rows.

   • Choose the first table to create a left outer join.
     
     
   • Choose the second table to create a right outer join.
     
     
   • Choose both tables to create a full outer join.

   Note   Some databases, such as Oracle, do not support full outer joins. For details, see Query Designer Considerations for Oracle Databases.

   For example, the following illustration shows the options you would choose for a left outer join:

   

When you specify an outer join, the Query Designer modifies the join line to indicate an outer join. For example, the following figure shows a join line that represents an outer join in which rows are included from the employee table:

In addition, the Query Designer modifies the SQL statement in the SQL pane to reflect the change in join type, as shown in the following statement:

SELECT employee.job_id, employee.emp_id,
   employee.fname, employee.minit, jobs.job_desc
FROM employee LEFT OUTER JOIN jobs ON 
    employee.job_id = jobs.job_id

Because an outer join includes unmatched rows, you can use it to find rows that violate foreign key constraints. To do so, you create an outer join and then add a search condition to find rows in which the primary key column of the rightmost table is null. For example, the following outer join finds rows in the employee table that do not have corresponding rows in the jobs table:

SELECT employee.emp_id, employee.job_id
FROM employee LEFT OUTER JOIN jobs 
   ON employee.job_id = jobs.job_id
WHERE (jobs.job_id IS NULL)