Create an Outer Join in a Query

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 (as reflected in the SQL pane) 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.

Create an outer join

1. Create the join.

   How?

   When you add two (or more) tables to a query, the Query Designer attempts to join them based on common data or on information stored in the database about how tables are related. However, if the Query Designer has not joined the tables automatically, or if you want to create additional join conditions between tables, you can join tables manually.

   You can create joins based on comparisons between any two columns, not just columns that contain the same information. For example, if your database contains two tables, titles and roysched, you can compare values in the ytd_sales column of the titles table against the lorange and hirange columns in the roysched table. Creating this join would allow you to find titles for which the year-to-date sales falls between the low and high ranges for the royalty payments.

   Tip

   Joins work fastest if the columns in the join condition have been indexed. In some cases, joining on unindexed columns can result in a slow query.

   1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
   2. Add to the Diagram pane the objects you want to join.

      How?

      When you create a query, you are retrieving data from a table, view, or function. To work with any of these objects in your query, you add them to the Diagram pane.

      Add a table, view, or user-defined function to the query

      1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
      2. In the Diagram pane, right-click the background and choose Add Table from the shortcut menu.
      3. In the Add Table dialog box, select the Tables, Views, or Functions tab.
      4. In the list of items, double-click each item you want to add.
      5. When you finish adding items, click Close.

      The Query Designer updates the Diagram pane, Grid pane, and SQL pane accordingly.

      Alternatively, you can drag objects onto the Diagram pane. You can drag a table, view, or inline function from the database window.

      You can also drag columns or tables from the Database Designer or paste them from the Clipboard.

      Tables and views are automatically added to the query when you reference them in the statement in the SQL pane.

      The Query Designer will not display data columns for an table, view, or inline function if you do not have sufficient access rights. In such cases, only a title bar and the * (All Columns) check box are displayed for the table, view, or inline function.

      Add an existing query to a new query

      1. If necessary, click SQL to show the SQL pane.
      2. In the SQL pane, type a right and left parentheses () after the word FROM.
      3. Open the Query designer for the existing query . (You now have two Query Designers open.)
      4. Display the SQL pane for the inner query û the existing query you are including in the new, outer query.
      5. Select all the text in the SQL pane, and copy it to the Clipboard.
      6. Click in the SQL pane of the new query, situate the cursor between the parentheses you added, and paste the contents of the Clipboard.
      7. Still in the SQL pane, add an alias after the right parenthesis. For more information on SQL aliases and subqueries, see the Microsoft SQL Server documentation.
   3. Drag the name of the join column in the first table, view, or function and drop it onto the related column in the second table, view, or function. You cannot base a join on text, ntext, or image columns.

      Note   The join columns must be of the same (or compatible) data types. For example, if the join column in the first table is a date, you must relate it to a date column in the second table. On the other hand, if the first join column is an integer, the related join column must also be of an integer data type, but it can be a different size. The Query Designer will not check the data types of the columns you use to create a join, but when you execute the query, the database will display an error if the data types are not compatible.

   4. If necessary, change the join operator; by default, the operator is an equal sign (=).

      How?

      By default, the Query Designer joins tables using an equal sign (an equijoin), which matches values in the two join columns. If you want, you can change the operator used to compare values in the join columns.

      1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
      2. In the Diagram pane, right-click the join line you want to modify, and then choose Properties from the shortcut menu.
      3. In the Join Line tab of the Properties dialog box, select a new operator from the list.

      Note   You can also change the operator in the SQL statement in the SQL pane.

   The Query Designer adds an INNER JOIN clause to the SQL statement in the SQL pane.

    Select the join line in the Diagram pane, and then from either the Query menu or the shortcut 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 Properties 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.

When you specify an outer join, the Query Designer modifies the join line to indicate an outer join.

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

Example of an outer join query used to find rows that violate foreign key constraints

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)