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 4

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
An inner join's ON condition retrieves only those records that satisfy the join condition. An outer join does the same thing but with the addition of returning records for one table in which there were no matching records in the other table.

There are three types of outer joins: left outer, right outer, and full outer. All outer joins retrieve records from both tables, just as an inner join does. However, an outer join retrieves all of the records from one of the tables. A column in the result is NULL if the corresponding input table did not contain a matching record.

 
Figure 3: A left outer join returns all the records from the left table, or the one side of a relationship.



The left outer join retrieves records from both tables, retrieving all the records from the left table and any records from the right table where the condition values match. If there are no matching values in from the right table, the join still retrieves all the records from the left table. Any columns from the right table that are unmatchedare left NULL. Consequently, the resulting recordset often appears to have incomplete records.

The circles in Figure 3 illustrate the concept of a left outer join. The left circle is the left table and all the records in this circle are returned. The intersection represents the matching primary/foreign key values between the two tables.

The following statement illustrates the usefulness of a left outer join:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerID

 
Figure 4: A simple left outer join helps you quickly spot inactive customers.

In Figure 4, the join returns all the customer records from the Customers table, and relates each customer to every order number in the Orders table. By adding the ORDER BY clause, you can easily spot those customers that haven't ordered anything. (The same statement works in the Access .mdb version of Northwind.)

A similar statement in Oracle 9i resembles the following:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerID

Earlier versions use the following:

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

 
Figure 5: A right outer join returns all the records from the right table, or the many side of a relationship.

The (+) symbol denotes the table (side) that may have no matching rows to the other table (side). Think of this as everything from the left plus matching values from the right. Notice the change between the two versions. Earlier versions are similar to the generic syntax discussed in the "SQL Standards" at the beginning of this article.

The right outer join is similar to the left outer join in that it retrieves all the records from one side of the relationship, but this time it's the right table. Only records where the condition values match are retrieved from the left. The circles in Figure 5 illustrate this join; notice that the only difference between this set of circles and the last is that the shaded circle is on the right—that right circle represents the right or many table in a relationship.



Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap