| Team Fly | |
The outer join of table temp2 returned all records from temp1 even if they had nonmatching rows in temp2. The outer join of table temp1 returned all records from temp2 whether or not they had matching rows in temp1. Lastly, we tried an outer join on both sides to see what would happen. This syntax would not work, and Oracle gave us a helpful error message (that's not always the case!). When we learn the union critical point later on in this chapter, we'll see that there's a way to do this with Oracle's syntax. However, let's move on to the ANSI outer join examples now, and we'll see that it is possible without writing a lot of code (that's a good thing!).
With Oracle9i, Oracle began down the journey to fully support ANSI SQL standards. To meet this goal, Oracle started the support of ANSI joins as discussed previously. We now present to you ANSI Outer Joins. As we just alluded to in Project 2.1, the ANSI outer join syntax allows us to perform right outer joins, left outer joins, and full outer joins.
ANSI Right Outer Joins As with the ANSI inner joins, the ANSI outer joins have moved the join to the from clause. A right outer join can be written with keywords right outer join or right join since outer is redundant. Rewriting our SALES and CUSTOMERS example from before with the ANSI syntax would produce the following:
SQL> select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold
2 from sales s right join customers c
3 on c.cust_id = s.cust_id
4 where c.cust_id in (1,80);
CUST_ID CUST_LAST_NAME PROD_ID QUANTITY_SOLD
---------- ----------------------------------- ---------- -------------
1 Kessel
80 Carpenter 127 1
80 Carpenter 36 1
As with the Oracle example, the SALES table nonmatched rows are returned. The main difference was that s.cust_id had the (+) notation before, and now we state that SALES will be right joined to CUSTOMERS. The join syntax is in the from clause, and the where clause contains only the selection criteria (in this case, only customer 1's and 80's records). This query can also be written with using or natural right join ANSI syntax. Go ahead and try that on your own. Make sure you get the exact same results as we did with the on example from the preceding example.
ANSI Left Outer Joins The ANSI left outer join works exactly the same as the right outer join and can be written using either left outer join or left join. As with the right outer join, the join on, join using, or natural left join styles are all available.
| Team Fly | |