Select Records from a Left Outer Join

File: SAMPLES\DATA\LOUTERJ.QPR

The query, LOUTERJ, in the Solution project combines information from the orders table and the customer table using a left outer join. Each result record has a fields for the order_id from the orders table and for the cust_id, company, country fields from the customer table as specified in the SELECT clause of the SELECT-SQL statement.

SELECT Customer.cust_id, Customer.company, Customer.country,;
 Orders.order_id;
 FROM testdata!orders LEFT OUTER JOIN testdata!customer ;
  ON Orders.cust_id = Customer.cust_id

Typically, a left outer join can answer two questions about the records in your database. Some of the questions this query could answer are:

• Which orders belong to which customers?
  
  
• Which orders do not have related customer information?

This information helps the database administrator determine which order records are missing information.

The left outer join returns all records from the table on the left of the join condition combined with the records from the table on the right of the condition that match the condition. The results set includes 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 an order_id that did not have a match in the customer table have NULL values in the fields that would otherwise hold values from the customer table. For example, if the record for order 11079, did not have any related customer records in the customer table, that record appears in the results with the value NULL in the fields, cust_id, company, and country.

The order for 11079 does not have a match any record in the Customer table

You can change the results of the query by specifying filters, a sort order, group, or other miscellaneous options for the query.