7.12.3 Outer join using LEFT OUTER JOIN
The following is an example of an outer join using LEFT OUTER JOIN.
-
Tables to be retrieved from
-
SELECT statement to be run
SELECT * FROM "USERSLIST" LEFT OUTER JOIN "SALESLIST" 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
-
Cartesian product of USERSLIST and SALESLIST (all row combinations)
Each row of SALESLIST is paired with each row of USERSLIST.
-
Retrieval results
The result in this example is a list of the following:
1. Customers who have purchased products (the same results from the INNER JOIN)
2. Customers who have not purchased products (in this case, customer ID U00212)
-