Sql JOINS and the Sql Server Management Studio Query Designer

There are a whole bunch of articles, blog entries and tutorials that seek to explain how SQL JOINS work. Some of them are excellent, and others are just confusing. The reason I am adding my go at trying to clarify JOINS to the mix is to highlight how proper use of the tools available to you can seriously reduce the chances of getting the JOIN syntax or type wrong. Since JOINS are all about how to relate data from one table to another, I thought it appropriate to illustrate the subject using Parents and Children (who may, or may not be related to eachother). So let's meet the families.

Peter has 2 children: Jo and Roger
Mary has 1 child: Susan
Alice has 2 children: Kevin and Rachel
John has 1 child: David
Jimmy has no children.
Sam and Ian have no parents. They are orphans.

This is how they appear in database tables: Parents and Children. Children are related to Parents by the AdultID.

INNER JOIN - All parents with children

If we open up the Query Designer in Sql Server Management Studio, and add the 2 tables, they are joined on the AdultID by default using an INNER JOIN. If they are not automatically joined (by the line that appears between the tables) you have not set AdutlID in the Children table as a foreign key. However, you can simply drag AdultID in the Children table, and drop it onto the AdultID in the Parents table to create the relationship.

Selecting AdultName and ChildName in the repsective columns generates the following SQL:

And when the SQL is executed, all parents with children are returned:

So who's missing? Batchelor Jimmy is nowhere to be seen (because he has no children) and nor are our orphans, Sam and Ian. INNER JOIN only returns rows from the two tables where there are matches based on the JOIN-predicate, which in this case is where Parents.AdultID has a matching record in Children.AdultID.

LEFT OUTER JOIN - All Parents, and their children if they have any

Right-clicking on the diamond in the middle of the Join line in the query designer reveals a context menu with some options for the JOIN. If we select "Select All Rows from Parents", the left hand side of the diamond fills out, and some new SQL is generated:

When this is executed, the LEFT OUTER JOIN returns all the parents (including Jimmy), irrespective of whether they have children or not:

It also returns all the children that have parents. In the case of Jimmy, he has NULL against the ChildName column, because he has no children.

RIGHT OUTER JOIN - All children, and their parents if they have any

We deselect "Select All Rows from Parents", and select "Select All Rows from Children" instead. The right hand side (or perhaps the RIGHT OUTER side?)of the diamond is now filled out, and the SQL changes to reflect this:

When executed, our orphans appear in the ChildName column, with NULL against the parent column. Jimmy is nowhere to be seen. I think he just hates kids.

FULL OUTER JOIN - All Parents and all children

Selecting both options from the JOIN property menu gives us a FULL OUTER JOIN:

This returns all records from both sides of the relationship, regardless of whether there are matching records:

CROSS JOIN - Cartesian product

If we remove the JOIN line:

we get a CROSS JOIN, which results, when executed, in the Cartesian product of both tables - 5 parents x 8 children = 40 rows. That means that every row in one table is matched to every row in the other table:

Notice the absence of a JOIN-predicate. There are some obscure uses for CROSS JOINS, including the creation of test data, but generally, they are not used.

Using JOINS to Find Unmatched records

If you study the results of, for example, the LEFT OUTER JOIN, you will notice that Jimmy was returned with a value of NULL in the Children table side of the resultset. Applying this as a filter to a JOIN query is very useful to finding records in one table that have no related records in the second table.

If we type "IS NULL" in the Filter column against the AdultID column of the Children table to the LEFT OUTER JOIN example, we end up with the following diagram and SQL:

and executing this results in Jimmy being returned on his own as the only record in the Parents table that doesn't have a matching record in the Children table:

Conversely, doing the same thing to the AdultID in the Adults table in the RIGHT OUTER JOIN example:

leads to this SQL and diagram:

which when executed results in both Orphans being returned, because again, they have no matching record in the Parents table:

From the questions posted to newsgroups and forums, it seems to me that plenty of people are either unaware of the existence of the Query Designer, or don't use it because they consider it some kind of cheating. For the former group, hopefully this article will show you something new. For the latter group, there is nothing wrong with using the tools available to you. It speeds up development and reduces your chance of errors.

Date Posted: Wednesday, November 28, 2007 1:19 PM
Last Updated: Tuesday, June 29, 2010 6:48 AM
Posted by: Mikesdotnetting
Total Views to date: 41486

7 Comments

Thursday, March 19, 2009 1:14 PM - Yohan

great article, simple, concise, and yet powerful.

Saturday, April 25, 2009 1:56 PM - Mahendran

simple superb article. thanxs

Tuesday, May 26, 2009 8:34 PM - Amit Ranjan

Simply Superb, well i am a follower of mikesdotnetting since a year...it provides essential things in a quite handy way...
best part is its language simple and easy to understand, no need to repeat...
You rock's...

infinity/10

Tuesday, September 8, 2009 9:26 PM - Goku Da Master

good article and easy to follow.

Monday, November 2, 2009 12:47 PM - The Zohan

Good stuff, very clear indeed.

Tuesday, October 23, 2012 5:26 AM - Hugh

Aha! I'm sitting here at 5 in the morning meddling with SQL!

Your article on Inner & Outer Joins has lifted a great fog of confusion. Many thanks for taking the time to write it.
Hugh

Friday, September 20, 2013 5:10 AM - Nashan

Very handy and simple article.Thanks for well explanation
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...