Select Records from a Full Outer Join Sample
File: ...\Samples\Data\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 include:
Which customers are in which countries or regions?
Which countries or regions do not have any of our customers in them?
Which customer records are missing country/region information?
This information might help someone decide if they should expand their business to other countries, change their marketing strategy in some countries or regions, or simply that some records in the database need the country/region information updated.
The full outer join returns all records from both tables and combines records that match the join condition. The result set includes the following three subsets of records:
Records matching the join condition that combine information from a record in each table.
Records from the country table that do not match the join condition.
Records from the customers table that do not match the join condition.
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 from 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.
You can change the results of the query by specifying filters, a sort order, group, or other miscellaneous options for the query.