7.12.5 Outer join using FULL OUTER JOIN

The following is an example of an outer join using FULL OUTER JOIN.

Tables to be retrieved from
SELECT statement to be run
```
SELECT * FROM "SALESLIST" FULL OUTER JOIN "USERSLIST"
                          ON "USERSLIST"."USERID"="SALESLIST"."USERID"
```
The resulting joined table will be the union of the following rows:
- The rows from the Cartesian product of the left and right table references (the Cartesian product of USERSLIST and SALESLIST) for which the search condition specified in the join specification (the underlined portion above) is true
- The rows from the Cartesian product of the left and right table references (the Cartesian product of USERSLIST and SALESLIST) such that the search condition is false for the rows of the left table and the rows of the right table are assigned null values
- The rows from the Cartesian product of the left and right table references (the Cartesian product of USERSLIST and SALESLIST) such that the search condition is false for the rows of the right table and the rows of the left table are assigned null values
1. Cartesian product of USERSLIST and SALESLIST (all row combinations)
  
  Each row of SALESLIST is paired with each row of USERSLIST.
2. Retrieve the rows for which the search condition is true.
  
  From the Cartesian product of USERSLIST and SALESLIST, this example retrieves the rows for which the search condition specified in the join specification is true (the same rows as the results of specifying INNER JOIN).
3. Retrieve the rows such that for each row of the left table reference where the search condition is false, the result of the right table reference is the null value.
  
  From the Cartesian product of USERSLIST and SALESLIST, this example retrieves the rows such that for each row of the left table reference (SALESLIST) where the search condition specified in the join specification is false, the result of the right table reference (USERSLIST) is the null value. In this example, this yields the row where the value in the USERID column of SALESLIST is U00026.
4. Retrieve the rows such that for each row of the right table reference where the search condition is false, the result of the left table reference is the null value.
  
  From the Cartesian product of USERSLIST and SALESLIST, this example retrieves the rows such that for each row of the right table reference (USERSLIST) where the search condition specified in the join specification is false, the result of the left table reference (SALESLIST) is the null value. In this example, this yields the row where the value in the USERID column of USERSLIST is U00212.
5. Retrieval results
  
  The result in this example is a list that includes the following:
  
  1. Customers who have purchased products (the same results from the INNER JOIN)
  
  2. Customers in the sales history who are not in the customer list (in this case, customer ID U00026)
  
  3. Customers who have not purchased products (in this case, customer ID U00212)

To Page Top