One of the first hurdles in understanding for someone trying to learn T-SQL is usually what the different Join types mean, and when to use them. Specifically the differences between Inner and Outer Join as these are the types most commonly used. If database mastery is what you seek, your SQL-fu will never be good enough until you know all the different join types and how they differ from each other. So right off the bat let’s just thow their names out there so you can get used to seeing them:

· Inner Join

· Left Outer Join

· Right Outer Join

· Full Join

· Cross Join

Ok, so that’s not too bad, there are only 5 different types of joins. So let’s set up an imaginary scenario that we can use to talk about them. We’re going to have one table named “System” that contains video game systems, and one called “Games” that contains related games for the systems. We’ll structure the tables so that there is a 1 to many relationship between our two tables like so:

I realize in reality this is probably a Many to Many relationship, but let’s not get ahead of ourselves. For the sake of simplicity in this example let’s assume games are mutually exclusive to systems, meaning they can only belong to 1 and only 1 system. So if you want Super Mario Brothers…you’ll have to buy a Nintendo etc.

Now let's load up our tables with some example data. For our System table we'll have the following data:

Now for our Games table (below) data let's add the following:

So these two tables are related based on the ID column in the System table and the SystemID column in the Games table. For example the Atari 2600 in the System table has an ID of 1. If we Look for rows with a SystemID of 1 in the Games table, we'll see that the first two rows match this criteria. As we go through our Join examples it will be helpful for you to know a few things about this data ahead of time

1. The Atari 2600 has 2 games and is the only system to have more than 1 game in the Games table.

2. Playstation 2 has no games at all in the Games table.

3. All other systems have exactly 1 game in the Games table.

Now that we've gone through setting up our scenario let's start talking about our join types.

INNER JOIN

An inner join will only show you ONLY the rows that match between two tables. It's also possible to have repeating values if a row has more than one match. let's take a look at an example query using Inner Join and its result set.

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [System] s

      INNER JOIN [Games] g

            ON s.ID = g.SystemID

the above query would have the following results.

There are a few things to note here. As we mentioned earlier, Inner Join will repeat a row if it has multiple matches. You can see this in action if you look at Atari 2600. It gets repeated because two matches were found in the Games table. The second noteworthy item involves the Playstation 2 System. It doesn't appear in our above result set at all because it didn't have any matches in the Games table. That's very important, if your results are missing data you expected to see, you may have eliminated it from the result set by using an Inner Join.

An easy way to think about Joins is to use a Venn Diagram.

If the circle on the left represented all the rows in the System table, and the circle on the right represented all the rows in the Games table. The rows that an Inner Join would return would be where the two circles intersect or have a match. This area is highlighted green in the Venn Diagram above.

LEFT OUTER JOIN

Left Outer Join will allow you to see ALL the rows from one table AND any matching rows from another table. If there are no matches then NULL will be displayed.

Q: So how do I know which of the two tables will have all of its values shown?

A: Whatever table gets listed first in the from clause is the table that gets all its values displayed. You can think of it quite literally as the table furthest left if you were to write your query all on one line.

let's take a look at an example query using Left Outer Join and its result set.

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [System] s

      LEFT OUTER JOIN [Games] g

            ON s.ID = g.SystemID

the above query would have the following results.

We see that our results are almost exactly the same. The only difference is now we see our Playstation 2 System, where we didn't before. Note that the "Game Name" is NULL for Playstation 2 since there are no matching values. Note also that Atari 2600 is still repeated because it found two matches. So if it's imperative that all values in a specific table be displayed in a result set, you'll want to make sure to use Left Join any time you join a table to it. To go back to our Venn Diagram, if we wanted to adjust it to represent a Left Join, it would look like this.

We've now shaded in all of the System circle, because no matter what, everything in the System table is going to appear in our result. Again, this is because we Left Joined the games table to the System Table.

RIGHT OUTER JOIN

Right Outer Join does pretty much the same thing as Left Outer Join, only in a different order. In general I don't see people use RIGHT OUTER JOIN very often, and I myself never use it. Right Outer Joins can be rewritten as Left Outer Joins and vice versa, by simply switching the order the tables are listed in. If we were to simply replace Left with Right in our last query like so:

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [System] s

      RIGHT OUTER JOIN [Games] g

            ON s.ID = g.SystemID

Our Venn Diagram for this query would look like this.

We would now be showing ALL the values from the Games table, and any matching values from our System table. If a game didn't belong to a system, the system would appear as NULL. (this doesn't happen in our test data however, because all games belong to a system) .

As mentioned before, we could make our Right Outer Join query return Exactly the same as our previous Left Outer Join Query by switching the order the tables appear in like so:

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [Games] g

      RIGHT OUTER JOIN [System] s

            ON s.ID = g.SystemID

FULL JOIN

A Full Join is like doing a Left Outer Join and Right Outer Join at the same time. I know....... I probably just blew your mind right out of your skull huh?....like trying to imagine what 4D looks like. So now all values from both tables you're joining will appear in the result set, and if they don't have a match on the other side of the join, they'll just display NULL. If you Understood Left and Right Outer Joins, Full Joins should be pretty easy to understand. Their Venn would look like this:

CROSS JOIN

Last but not least, a Cross Join will take the each row in the first table and match it up with every row in the second table. It does this by performing a Cartesian Product on the two tables. Since all rows from the first table are going to be matched to all rows from the second table and vice versa, there's no need to specify an ON clause in your queries FROM statement when using a Cross Join. Cross Join results get very big very fast, If we were to cross join our test data tables we'd already be up to 49 rows, so be careful. It's pretty rare to find a use for a Cross Join....although it does happen.

Well, hopefully that sheds a little light on the whole JOIN situation for someone starting out.