SQL Joins And You

What are SQL Joins?

A SQL JOIN is used when you want to combine a row of records from multiple tables based on a common column value between the records.

A side note before moving forward: This blog post assumes you know basic SQL syntax, and have a general understand of relational databases. This isn’t some super technical read, but I thought it would be useful to align expectations up front.

What does that mean?

Well let’s say you have two tables in your database: users and comments.

The database schema is set up so that there is a one:many relationship. A user can have zero, or more comments. This means for each user record there is either zero, one, or many comments records that have a foreign key column that points to that users record primary key column.

If you want to visualize that here is a glorious diagram:

A user has many comments. The arrow means that the user_id column in comments references the id column in users.

For reference we can say our database currently looks like so:

If your goal is to return data from your database where you want to organize each user with the comments they made that is the perfect use case for a SQL JOIN.

I wonder what that translates to in SQL code.

Wonder no more. Here you go:

The ON clause contains the condition in which to join the rows of records with each other.

In this case for every record in users where the id column is equal to the user_id column for a comment the name of the user will be returned along with the body of the comment. These columns will be joined together in a single row like so:

Types of Joins

1. Inner Join (aka JOIN)

The first (and most common) type of JOIN I’ll talk about is an INNER JOIN. Is is sometimes referred to as a Simple JOIN, or just JOIN.

An INNER JOIN will return rows of data on both sides of the join based on the condition specified in the ON clause. If the records do not satisfy this clause they will not be returned.

When Would I Use An INNER JOIN?

An INNER JOIN is demonstrated in the introduction section above. If you look at the results of the query you will notice that the user Cynthia is not returned because she does not have any associated comments.

A user with no comments will not be returned by the query. Similarly a comment that does not belong to a user will not have its record returned either.

So when should you use it? When you require your query to return data in a situation that is similar to the above. That is if you only want rows of data that are returned, and joined where both need to satisfy a certain condition.

2. Left Join (aka Left Outer Join)

A LEFT JOIN, also know as a LEFT OUTER JOIN will return all records from the table specified in the FROM clause (the left table), joined with records from the right table that satisfy the ON clause.

This means that there may (and probably will be) rows of records returned where the record from the left table is not joined with an associated record from the right table.

Here’s a query that does just that:

users is the left table with comments being the right

And here is the data returned by our database:

The query above returns the name of each user as well as how many comments they have made.

Contrary to the INNER JOIN from above LEFT JOIN returned ALL the values for the name column in ever user record even if that user did not have a comment. This is demonstrated by the fact that Cynthia was returned, but her comment_count is zero.

When Should I Use A LEFT JOIN?

When you need to all records from the left table, and you only want to join those rows with records in the right table that satisfy the condition in the ON clause.

3. Right Join (aka Right Outer Join)

A RIGHT JOIN, also know as a RIGHT OUTER JOIN is essentially the exact opposite of a LEFT JOIN. Because of this I won’t go in to any additional detail about it.

When Should I Use A RIGHT JOIN?

When you need to all records from the right table, and you only want to join those rows with records in the left table that satisfy the condition in the ON clause.

4. Cross Join

A CROSS JOIN, also know as a CARTESIAN JOIN will return all records from both tables where for every record in one table it is joined together in a row with every record from the other table.

Stated another way a CROSS JOIN is essentially an INNER JOIN without the condition to evaluate in the ON clause.

Heres an example using the existing sample database from the previous three joins:

Note how JOIN was not actually used here. SQL infers that you want a CROSS JOIN when you specify multiple tables in the FROM clause.

And here is the result of that query:

So many rows!

As you can see for every user record the users name is joined in a row with the body of every comment.

Until Next Time

That’s it! I hope this brief tour of SQL JOINs shed some light as to what they do.

If you feel anything here was inaccurate, or could use some clarification please let me know!