Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Getting the Right Data with SQL Joins : Page 5

Associating tables in SQL is usually a breeze. But what happens when you find yourself looking at the completely wrong set of records and you don't know why? Find out how correctly applying joins can help you understand and avoid these mysterious data returns.


advertisement
The Outer Joins (cont'd)
The next example relates all the customers to the current orders and by doing so exposes customers that haven't ordered. You can run the same statement using a right outer join to exclude the two Null valued records, but an inner join will do the same. If the Orders table contains any orphans (records without a matching value), a right outer will find them. The Orders table doesn't contain orphans (which is good for the database, but bad for these examples), so just suppose it does. If you delete the first record in the Customers table—Alfreds Futterkiste, the following right outer join turns up a few orphans:

SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID FROM CustomersCOPY RIGHT JOIN Orders ON CustomersCOPY.CustomerID = Orders.CustomerID ORDER BY CustomersCOPY.CustomerID

The orphans sort to the top of the results, as shown in Figure 6. Included here is a copy of the Customers table so as not to alter the original content and design of Northwind. If you want to work through the example yourself, work with a copy of the Customers table as has been done here.

 
Figure 6: A right outer join turned up a few orphans that we created on purpose, just for this example.



A review of the complete recordset reveals that the right outer eliminates the two Null-valued records for PARIS and FISSA from the earlier example. These were the two inactive customers. Remember, the right outer retrieves all the records from the right table, which in this case is the Orders table. There are no orders for either of the above companies in the Orders table, and the join can't show data that doesn't exist. However, the orphans do have orders in the Orders table, even though the join can't match them to a customer in the CustomersCOPY table. The right outer join includes the orphans despite the relationship's inability to match each orphan's foreign key value to a primary key value in CustomersCOPY.

A similar Oracle 9i statement resembles the following:

SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID FROM CustomersCOPY RIGHT OUTER JOIN Orders ORDER BY CustomersCOPY.CustomerID

Earlier versions use the (+) syntax in the form:

SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID FROM CustomersCOPY, Orders WHERE CustomersCOPY.CustomerID(+) = Orders.CustomerID ORDER BY CustomersCOPY.CustomerID

Experimenting with the outer joins may lull you into believing that they're interchangeable, but they aren't. Remember, the left join retrieves all the records from the table on the left, the right join retrieves all the records from the table on the right. Keeping that in perspective should help you decide which join you need.

 
Figure 7: A full outer join combines all records from both tables.

Now that you know the nature of the left outer and right outer joins, you might be able to anticipate what a full outer join does. The full outer join retrieves all records from both the left and the right table. Most likely, you won't run into a need for this join very often. A full outer join is best illustrated by slightly changing the previous example. Simply run that same statement as a full outer join, as shown in Figure 7. Review the entire recordset to see that the results now include both the orphans and the two inactive customers. The orphans are sorted to the top as they were in Figure 6. The first inactive customer is FISSA—notice that the OrderID value is Null. Scroll down to find PARIS, the other inactive customer.

A comparable Oracle 9i statement resembles the following:

SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID FROM CustomersCOPY FULL OUTER JOIN Orders ON CustomersCOPY.CustomerID = Orders.CustomerId ORDER BY CustomersCOPY.CustomerID

Or, rely on the new USING clause in the form:

SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID FROM CustomersCOPY FULL OUTER JOIN Orders USING (CustomersCOPY.CustomerID) ORDER BY CustomersCOPY.CustomerID

There isn't an equivalent in earlier versions of Oracle. Earlier versions use WHERE statement with the plus sign (+) syntax, and one of the rules is that you can't use the symbol on both sides.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap