Posted by: the author | October 5, 2012

SQL Joins Analogy

I was reading through Microsoft SQL Server 2008 Bible (by Paul Nielsen, Uttam Parui and Mike White) today and found a really nice, simple way of looking at how joins work:

A Join Analogy

When I teach how to build queries, I sometimes use the following story to explain the different types of joins. Imagine a pilgrim church in the seventeenth century, segmented by gender. The men all sit on one side of the church and the women on the other. Some of the men and women are married, and some are single. Now imagine that each side of the church is a database table and the various combinations of people that leave the church represent the different types of joins.

If all the married couples stood up, joined hands, and left the church, that would be an inner join between the men and women. The result set leaving the church would include only matched pairs.

If all the men stood, and those who were married held hands with their wives and they left as a group, that would be a left outer join. The line leaving the church would include some couples and some bachelors.

Likewise, if all women and their husbands left the church, that would be a right outer join. All the bachelors would be left alone in the church.

A full outer join (covered later in this chapter) would be everyone leaving the church, but only the married couples could hold hands.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: