File: SAMPLES\DATAT\FOUTERJ.QPR
The query, FOUTERJ, in the Solution project combines information from the country table and the customer table using a full outer join. Each result record has a field for each of the fields from the country table, and the country
and cust_id
field for the field from the customer table as specified in the SELECT clause of the SELECT-SQL statement.
SELECT Country.*, Customer.country, Customer.cust_id; FROM testdata!customer FULL JOIN country ; ON Customer.country = Country.country
Typically, a full outer join answers three questions about the records in your database. Some of the questions this query could answer are
This information might help someone decide if they should expand their business to other countries, change their marketing strategy in some countries, or simply that some records in the database need the country information updated.
The full outer join returns all records from both tables and combines records that match the join condition. The result set includes three subsets of records.
Because each record in the results has the same fields, the records that did not have a match in the other table have NULL values in the fields that would otherwise hold values the other table. For example, if a record for the country, Russia, did not have any related customer records in the customer table, that record appears in the results with NULL as the value of the field, cust_id
and country_b
.
NULL values appear in fields for non-matching records.
You can change the results of the query by specifying filters, a sort order, group, or other miscellaneous options for the query.