Skip navigation

Jump Start: Left and Right Outer Joins

In the last Jump Start column (“Inner and Outer Joins,” InstantDoc ID 95651, http://www.sqlmag.com/Article/ArticleID/95651/sql_server_95651.html), you learned the difference between INNER and OUTER joins. An INNER JOIN uses the table on the left side of the join as the basis for creating a result set that uses common data values in both the tables to the left and right of the join.

Conversely, an OUTER JOIN uses the tables on the right side of the join as the basis for creating a result set. For the INNER JOIN, if no matching data from the table on the left is in the table on the right, then the data from the left table isn’t included in the results. For the OUTER JOIN, if no match is found in the table on the left side of the join, then the data from the table on the right side of the join isn’t included in the result set.

However, you might wonder how you can control other conditions in which you want data returned. For instance, there may be times when you want data returned even if there's no match in the joined table. That’s where the other types of joins come in. In addition to the INNER and LEFT OUTER joins, there are also the RIGHT OUTER and FULL OUTER joins. A RIGHT OUTER JOIN is much like a LEFT OUTER JOIN, except that the table used as the basis of the join is reversed. With a RIGHT OUTER JOIN data from every row from the table on the right side of the join will be returned. For rows that have no matching data on the left, NULL values will be returned. You can see an example of the RIGHT OUTER JOIN in the following example.

SELECT c.CustomerID, c.CompanyName, o.OrderID FROM Customers c RIGHT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID

As its name implies, the FULL OUTER JOIN incorporates all of the rows in both tables and will return NULLs for any values missing on either side You can see an example of the FULL OUTER JOIN syntax in the following example.

SELECT c.CustomerID, c.CompanyName, o.OrderID FROM Customers c FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish