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 are
This information may 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 two subsets of records.
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
.
The record for order 11078 without a matching record in the EMPLOYEE table.
You can change the results of the query by specifying filters, sort order, group, or other miscellaneous options for the query.