Select Records from a Right Outer Join Sample
File: ...\Samples\Data\Routerj.qpr
The query, ROUTERJ, in the Solution project uses the testdata database, and combines information from the orders table and the employee table using a right outer join. Each result record has a field for the order_id from the orders table and for the lastname field from the employee table.
SELECT Orders.order_id, Employee.last_name;
FROM testdata!employee RIGHT OUTER JOIN testdata!orders ;
ON Employee.emp_id = Orders.emp_id
Typically, a right outer join can answer two questions about the records in your database. Some of the questions this query could answer include:
Which orders were entered by which employees?
Which orders do not have an employee specified?
This information might help track specific orders and identify orders that do not have an employee specified as placing the order.
The right outer join retrieves all records from the table on the right of the join condition combined with the records from the table on the left that match the join condition. The results set includes the following two subsets of records:
Records matching the join condition that combine information from a record in each table.
Records from the orders table that do not match the join condition.
Because each record in the results has the same fields, the records with a lastname that did not have a match in the orders table have NULL values in the field that would otherwise hold values from the orders table. For example, if a record for order number 11078 did not have any related employee records in the employee table, that record appears in the results with the value NULL in the field, lastname.
You can change the results of the query by specifying filters, sort order, group, or other miscellaneous options for the query.