| Team Fly | |
ANSI Natural Join ANSI SQL also gives us a third join alternative, the natural join. In this case, the columns to be joined are not specified but rather are resolved by Oracle. They must be similarly named in the tables to be joined. As always, additional selection criteria can be specified in the where clause.
select cust_id, c.cust_state_province,
s.quantity_sold, p.prod_name
from sales s
natural join customers c
natural join products p
where prod_id = 117;
As we found out with the using syntax, we couldn't use the table alias qualifier on the cust_id column. If we did, we would get an ''ORA-25155: column used in NATURAL join cannot have qualifier" error message.
Although it would be very poor database design, it's entirely possible that a similarly named column could exist in different tables but have no relationship to each other. Be careful while naturally joining tables to make sure that it makes sense to join them. While this could just as easily happen with a regular Oracle join, the simple act of having to specify which columns to join could force you to go through this thought process. It's an important fact to know your tables and what you want to accomplish with the joins.
Unlike an inner join, which only returned records that had matching values for a specific column in both tables, an outer join can return results from one table where the corresponding table did not have a matching value.
In our sample set of data, there are a number of customers that haven't recorded any sales. There are also a number of products that haven't been sold either. These examples will be used in the following explanation of Oracle and ANSI outer joins.
In order to find rows from one table that don't match rows in another, known as an outer join, Oracle presented us with the "(+)" notation. The "(+)" is used in the where clause on either of the tables where nonmatching rows are to be returned.
In order to illustrate this, we have found that cust_id = 1 does not have any sales, while cust_id = 80 has exactly two. Let's take a look at what happens when we select these two customers from the CUSTOMERS table and request some SALES table details if they exist:
SQL> select c.cust_id, c.cust_last_name, s.prod_id, s.quantity_sold 2 fromcustomers c, sales s 3 where c.cust_id = s.cust_id(+) 4 andc.cust_id in (1,80);
| Team Fly | |