SQL Joins Explained Visually: The 3 Ring Binder Model

The following is a guest post by Matt Salerno and originally appeared on his blog. Matt is currently a student a The Flatiron School. You can learn more about him here, or follow him on twitter here.

Any programmer worth their weight in bitcoins is going to need to know a bit about databases. If you’re not familiar with databases, you should take some time to read up on them, but hurry back, because for this post we’re going to be talking about the different kinds of JOINS. ‘Joins’ are a method of linking multiple tables in a database together so we can access values from them with a single query. Seems simple enough, but joins can be confusing. Since tables in a database aren’t tangible things, it can be difficult to understand the links we create between them. I for one find it difficult to really wrap my head around a concept until I can draw it, which made for some interesting moments in high school sex ed, but also led me to develop a method for visualizing how the different kinds of joins work! Let’s have a look:

3 RING BINDER

Picture the two tables in our database as two pieces of looseleaf. The blue lines represent our rows, or records. The dashed lines represent our columns.

Now, using the SQL syntax for sqlite3, we’ll start writing our join by stipulating which tables we’d like to link.

We’ll go over INNER JOIN in a second, but for now just picture us putting our two pieces of looseleaf into a three ring binder. We’re selecting from both persons (left table) and orders(right table), and linking them with a join.

Now that our pages (tables) are in a binder (joined), we need to stipulate which column to link them on.

Here, we’re saying that we should link the rows in persons and orders based on the value of the ‘id’ column in the person table, and the ‘pid’ column in the orders table. When the values in those two columns match, we can assume a correlation between the two records.

Now we’ve joined our tables. But there are a few different ways to join two tables, which will effect which rows are returned. Let’s have a look:

Inner Join:

An inner join, or left inner join, will produce only the records for which there is a match from tablea (persons) and tableb (orders) (records produced are highlighted):

Left Outer Join:

A left outer join will produce a complete set of records for tablea (persons), with matching records from tableb (orders) when they exist. If there’s no match from tableb, the right table’s join column value will be ‘null’.

Right Outer Join:

A right outer join will produce a complete set of records for tableb (orders), with matching records from tablea (persons) when they exist. If there’s no match from tablea, the left table’s join column value will be ‘null’.

Full Outer Join:

A full outer join will produce the complete set of records from both tables. Wherever there is no match, the value will be ‘null’.

Conclusion:

Hopefully this visualization will help you wrap your head around database table joins, (and maybe conjure fond memories of decorating your Five Star™ binders…). For more on databases and visualizations for joins, you can go here, or here for some great insights.

Disclaimer: The information in this blog is current as of October 22, 2012. Current policies, offerings, procedures, and programs may differ.