Database Fundamentals #19: JOINS

The last Database Fundamentals post introduced the SELECT and FROM commands. We’re going to start using JOIN operations shortly, but first, let’s explore the idea behind joins. The very concept of relational storage that is the foundation of SQL Server requires you to related one table to another.  You do this through a operation called JOIN. There three basic types of JOINS, INNER, OUTER, and CROSS. Think of them like this. It’s all about relationships. The relationships are only ever between two sets of data. Yes, you can combine lots of tables together through a query, but each JOIN relationship will be between two sets of data.

Types of Joins

If you take two sets of data and represent them as two circles, they might look like this. An INNER JOIN takes the two sets of data and finds the matching values to return in the query. This shows a graphical representation of an INNER JOIN, the place where the data crosses:

An INNER JOIN is also referred to as an equi-join because it is predicated on the values equaling each other to arrive at the combined data set. The INNER JOIN is probably one of the most commonly used types of relationships between two sets of data.

The next type of JOIN is the OUTER JOIN. These relationships are either a LEFT JOIN or a RIGHT JOIN. What the left and right refers to is which side of the relationship is driving the data. This shows a LEFT JOIN. All the data from the LEFT data set will be returned and only the data from the other data set that matches will be returned.

A RIGHT JOIN would work just the opposite way. It would return all the data from the RIGHT data set and then only that data from the LEFT data set that matches.

A CROSS JOIN is something completely different. In this case, all the data from the first data set is combined with all the data in the second data set. This means that each row of the first data set is combined with all the rows of the second data set. This graphic attempts to show this by multiplying Data Set A by all of Data Set B:

The other common name for a CROSS JOIN is a Cartesian product. This can actually come about when you forget to include JOIN criteria in your TSQL statement. This type of JOIN is rarely used on purpose.

There are other types of JOINs defined by the TSQL standard, but they are not arrived at through operations in the TSQL language as defined by SQL Server, so I won’t bother covering them here. Most of them are actually somewhat theoretical anyway. Most of the time, in most circumstances, these three types of JOINS will get you the data you need.

Conclusion

With a basic understanding of what to expect when you start using the JOIN operator, the data you retrieve with your queries will make a lot more sense.