This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Database Basics Part Four - Table Joins Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

In an effort to provide information for those of you who are newcomers to databases, especially with the wonderul new Sql Server Express, I have committed myself to about half a dozen posts or so targeted directly at you to help you understand and build better databases.  Stay tuned to this blog for weekly updates to this series, or subscribe to the rss feed.

Previous articles in this series:

• The ACID Model – the cornerstone of databases and database transactions.
• Normalization – the logical design and storage of data.
• Isolation – degrees of seperation among concurrent transactions.

Today we discuss joins.  Joins allow an easy way for us to retrieve information from multiple tables, presenting the data as a single table, based on the logical relationships among the tables.  Typically joins utilize columns that have an existing foreign key defined on one of its columns and an associated column in another table.

INNER JOIN

The inner join specifies that all matching rows, and only matching rows, are returned in the result set.  This is the default join type, although not the most common.  Let’s take a look at an inner join.

Let’s say I have the following 2 tables: 

For a quick explanation, if an actual constraint (relationship) between the color column of the cars table and the colorid column of the colors table existed, we wouldn’t have been able to put in the Kia Sedona with a color of 5, because 5 doesn’t exist in the lookup table.  For demonstration purposes, we are going to overlook the fact that we should have a relationship between the two tables.

To demonstrate the inner join, let’s create a quick sql query that pulls back the rows from the cars table, along with the color description that matches from the colors table.

And our resulting set looks like 

You will notice two things.  First, the Kia Sedona did not come back in the result set.  This is because there was not a matching color in the colors table.  Second, the color blue was not returned in the result set.  This is because there is not a matching car with that color in the cars table.  This is the result set of an inner join.

FULL JOIN

The full join returns all rows, matching or not, into a result set.  This means that even rows that do not meet the condition of the join are returned, and the output columns that come from the other table are set to null.

A full join sql query for the same tables from Fig. 1.

The matching result set is

All rows from both table were returned in the result set, including the ones that did not match our join statement.

LEFT JOIN

In order to understand the left join, you have to understand what left means in the query.  In a join condition, the left table is the table listed to the left of the JOIN statement.  The right table is, you guessed it, the table listed on the right.  In both queries above, the left table is the cars table and the right table is the colors table.  The left join is the most common join used.

A left join specifies that all rows from the left table that do not meet the join condition are returned in the result set, and the output columns from the joining table that do not match the join condition are NULL.  Non-matching rows from the right table are not returned.

From the colors table, the color Blue was not returned in the result set.  The Kia was returned, however, because we specified that all rows from the left table should be returned.

RIGHT JOIN

The right join works just like the left join, except that the full result set is returned from the right table, and non-matching rows from the left table are not returned.

 The results of our right join query are

This time, we got the Blue color from the colors table returned, but not the Kia Sedona.