The INNER JOIN operator is probably one of the most widely used tools we have when querying databases. If you plan on working with databases for more than 5 minutes, you should plan on encountering an INNER JOIN very early on in your career. Knowing what an INNER JOIN is and how it works will help you write meaningful queries with little code.
If you want to learn about other extremely common querying tools available to us, check out the following tutorial:
Top 6 tools you should know for writing AWESOME Select statements
This guide will give you an in-depth look at the INNER JOIN operator while also giving you an introduction to JOINs in general and how they can help us.
In this tutorial, we will be discussing the following topics:
- What is an INNER JOIN?
- How can an INNER JOIN help us?
- What is the syntax for an INNER JOIN?
- Are there different kinds of JOINs?
- Tips and tricks
Also, check out this FREE Ebook:
FREE Ebook on SQL Server JOIN Operations!
This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!
Let’s start from the top
1. What is an INNER JOIN?
An INNER JOIN is used in a SELECT statement to give you access to columns in two or more tables in order to present meaningful information to the user.
An INNER JOIN allows you to basically use a single SELECT statement to pull column information that would otherwise require you to write two or more SELECT statements.
If we get the same information using a single SELECT statement, instead of several, we will have written a better, cleaner solution.
2. How can an INNER JOIN help us?
Imagine your boss comes up to you Monday morning and says,
“Good morning, I need you to do something for me. Our Database Admin is out sick today, and I know you have been learning a thing or two about databases. Since you have access to our company database, I need you to get me all the information we have on an order, Order # 8. Think you can do that?”
To which you respond,
“You can count on me!”
You’ve been waiting for this opportunity. You’ve been waiting to show your boss how valuable you can be.
You go to SQL Server Management Studio, find the company database, and run your query against the Orders table:
You’ve done it. You send the screenshot to your boss. They invite you to their office and say,
“Um, what’s this?”
You say, “Why, it’s the details for Order # 8, just like you asked! Looks like Customer # 2 bought Product #4 on October 6th. Need anything else?”
“Well, yeah. Who is Customer # 2? What was Product # 4? How much MONEY did we make from the sale?!”
“Oh, right, umm, let me get back to you…”
You go back to you desk to do more research, and realize the Customer ID in the Orders table maps to the Customers table. So you query that table, looking for Customer # 2:
Ok, great, now you know who Customer # 2 is: Andrew Bluefield
But you need to find out what Product they bought, and how much it costs. You realize the Product ID in the Orders table maps to the Products table. So you query that table too, looking for Product # 4:
It looks like Andrew Bluefield bought a set of Side Tables, and he paid $265.20. You send this screenshot to your boss. They reply with a single word: “thanks”.
You’ll understand if they don’t ask you for database help anymore.
You blew it.
Remember what we said in the definition of an INNER JOIN: They can help you “present meaningful information to the user”
Seeing the Customer ID is not as helpful as seeing that customer’s First and Last name. Seeing a Product ID is not as helpful as seeing the name of the product and how much it costs. A JOIN can help us gather those meaningful details.
Remember what else I said about JOINs: “If we get the same information using a single SELECT statement, instead of several, we will have written a better, cleaner solution.”
We had to write three separate queries to get all the information our boss wanted. It would be so much better if we could have gotten all that same information into a single result set, instead of three.
Wouldn’t you agree the following single result set is very meaningful?
This is why we need to learn how to write JOINs!
3. What is the syntax for an INNER JOIN?
Here it is:
SELECT <column list> FROM <table one> INNER JOIN <table two> ON <table one>.<column to match> = <table two>.<column to match>
This layout is a bit weird, so let’s not dwell on it. We’re going to break this down and look at some examples, which is honestly the easiest way to understand INNER JOINs.
One thing I did want to point out is how the word ‘INNER‘ is optional. If you just had the word ‘JOIN‘ by itself, SQL Server would know you want to do an inner join (versus an outer join, which we’ll discuss later).
Let’s first look at all the rows and columns in our Orders table:
We already understand how unhelpful this data alone is.
How nice would it be to add the customer’s First and Last name to the result set?:
(I’m purposefully not showing you the query that created this result set. We’re going to figure it out together)
This is already a more meaningful result set.
Through our research, we know the CustID column is a reference to the Customers table. Let’s see the data in the Customers table:
The Customers table has it’s own CustID column.
Knowing this, let’s talk about what we want, in plain English:
“I want to see some columns from both the Orders table and the Customers table. For each CustID in the Orders table, I want to see if there is a matching CustID in the Customers table. For CustID’s that exist in both locations, pull the FirstName and LastName from the Customers table (as well as all the columns from the Orders table)”
Believe it or not, this paragraph contains everything we need to know to construct our JOIN.
Let’s break it down:
“I want to see some columns from both the Orders table and the Customers table.”
This tells us the two tables we will be using in our JOIN: Orders and Customers
Going back to our syntax, let’s start plugging in some values:
SELECT <column list>
FROM Orders
INNER JOIN Customers
ON <table one>.<column to match> = <table two>.<column to match>
Because this is an INNER JOIN, it does not matter which table is used in the FROM clause and which is used in the INNER JOIN clause. For other types of JOINs, however, it absolutely matters.
Let’s think about the next sentence:
“For each CustID in the Orders table, I want to see if there is a matching CustID in the Customers table.“
So we want to match the Orders CustID column to the Customers CustID column.
Let’s walk through the first few rows in the Orders table to show you the process. As a reminder, here are the first few rows in the Orders table:
Remember, we want to match CustID values here with CustID values in the Customers table. So, starting from the top, is there a CustID in the Customers table with a value of 3? Yes there is!:
As far as the INNER JOIN is concerned, what does this mean? Because CustID #3 exists in both tables, CustID #3 will be returned in the final result set, and we will have access to all the columns for the row in both tables.
Let’s drive the point home with the next few CustID’s in the Orders table. The next CustID appears to be 1. Is there a matching row in the Customers table with a CustID of 1?
Yes!
So again, CustID #1 will be returned and we will have access to all columns from both tables.
The next CustID in the Orders table is 1 again, and we just talked about it. This row will be returned a second time, which is fine (it’s still a different order, remember?).
This process continues all the way down the Orders table, looking at each CustID to see if there is a match in the Customers table. Matching rows get returned, and we have access to all columns from both tables.
Going back to the syntax of an INNER JOIN, how do we outline we want to return rows where the Orders.CustID column value matches the Customers.CustID column value? This is what the ON clause is for:
SELECT <column list>
FROM Orders
INNER JOIN Customers
ON Orders.CustID = Customers.CustID
We use the ON clause to outline the columns we want to match for our INNER JOIN.
Finally, this last sentence:
“For CustID’s that exist in both locations, pull the FirstName and LastName from the Customers table (as well as all the columns from the Orders table)”
Remember, for matching rows, we have access to all columns from both tables. In our column list, we’ll outline all the columns from the Orders table, and just the FirstName and LastName columns from the Customers table.
SELECT Orders.OrderID,
Orders.CustID,
Orders.ProdID,
Orders.Qty,
Orders.OrderDate,
Customers.FirstName,
Customers.LastName,
FROM Orders
INNER JOIN Customers
ON Orders.CustID = Customers.CustID
If we wanted, we could have also outlined the Customer.CustID column, but that would have been redundant. We already display the Orders.CustID column.
So our query is complete. This is the query that returned the result set I showed you earlier. Here is the whole thing:
There is a common diagram you will see that helps you conceptualize the idea of an INNER JOIN. Here it is:
The circle on the left represents all the data in your first table (in our example, the Orders table). The circle on the right represents all the data in your second table (in our example, the Customers table).
Where they intersect is where the two tables have matching data (in our example, where Orders.CustID = Customers.CustID).
The INNER JOIN will only return data in the intersection.
Here is the diagram again with our table examples:
Understand presently, in our example, all our CustID’s in the Orders table have a match in the Customers table, which is good.
But this diagram helps us understand what happens when a CustID in the Orders table does NOT have a matching row in the Customers table.
To begin with, from a business perspective, this isn’t a good situation to be in, right? You’re saying you have an order placed for a customer that doesn’t exist? Yikes!
You would most likely want to make sure this situation physically cannot happen through something called a Foreign Key Constraint. Basically, if a Foreign Key Constraint existed, the system would not let you create an order for a customer that doesn’t exist.
Read more: SQL Server Foreign Key: Everything you need to know
But let’s say we don’t have a Foreign Key Constraint, and some bozo entered an order for a customer that doesn’t exist.
Here is the order in the Orders table:
As a reminder, here is our list of Customers:
CustID #750 isn’t there!
So again, through the rules of an INNER JOIN, we only return the row if the column value we are matching on exists in BOTH tables. So, since CustID #750 does not exist in BOTH tables, this value is NOT returned from the INNER JOIN. Our result set would still look like this:
No CustID 750! Remember the diagram: We only return matching rows.
You can have more than one INNER JOIN in a query
Moving on, there is something else you need to know about INNER JOINs: You can have more than one INNER JOIN in a query. This allows you to join on multiple tables.
In our result set, what if we wanted to see the Product Name too? Like this:
Through our research, we know the ProdID column in our Orders table is a reference to the Products table. Let’s see the data in the Products table:
Folks, it’s the same idea. We want to see Product Names for products that have a matching ProdID between the Orders table and the Products table.
To join on another table, you would just add another INNER JOIN clause with it’s own ON clause to your query, like this:
Nice. Finally, we can still add a WHERE clause to our query. Remember how our boss wanted information for Order # 8? That’s an easy WHERE clause:
POP QUIZ:
Remember in point # 2 when I showed how the following result set is very meaningful?:
Before I show you the query that generated this result set, can you guess what the query will be?
Hint: We are doing a join between the Orders, Customers, and Products table, and we are filtering on Order # 8
Hint: My query uses aliases for the FirstName and LastName columns from the Customers table, as well as aliases for the ProdName and Price columns of the Products table.
Without further ado, here it is:
Well done.
4. Are there different kinds of JOINs?
Yes.
Here is the full list:
- INNER
- LEFT OUTER
- RIGHT OUTER
- FULL OUTER
- CROSS
This tutorial obviously focuses on the INNER JOIN, which is the most common join and the one you should learn first.
The second most common type of join is the LEFT OUTER JOIN. In our example of CustID 750, if we wanted order information returned for this CustID, despite not having a matching row in the Customers table, we would need to use a LEFT OUTER JOIN.
Another diagram will help:
A LEFT OUTER JOIN will return all rows from your first table, even if there is no match in the second table. Here is the left outer join in action:
So the query returned all rows from the first table (aka the left table), which is Orders. But since there isn’t a matching CustID in the Customers table, what values is it supposed to use for the FirstName and LastName columns?
Well, NULL represents the absence of information, which is exactly the situation we find ourselves in. Therefore, those columns are given a value of NULL.
Learn more about the LEFT OUTER JOIN in the full beginner-friendly tutorial:
SQL Server LEFT JOIN. The Ultimate Guide for Beginners
Also check out the full tutorials on the RIGHT OUTER JOIN and the CROSS JOIN:
RIGHT JOIN in SQL Server: Explained for Beginners
CROSS JOIN in SQL Server: Explained for Beginners
5. Tips and tricks
Here is a list of helpful tips and tricks you should know when working the the JOIN operator:
Tip # 1: You should use table aliasing when you use the INNER JOIN operator
Take a look at the inner join query again:
How many times did we need to repeat the word “Orders“? About a million. Several times in the column list, then another in the ON clause. You should give your tables an alias, and use the alias instead of the table name, like so:
The letters ‘O‘ and ‘C‘ are much easier to type repeatedly. It’s a very common practice to use a single letter as your alias, and normally that letter will be the first letter of the name of the table.
While we’re talking about cool things to do in the column list, notice we pulled all 5 columns from our Orders table. If we know we want all columns from one of our tables, we can use a shortcut. Check this out:
Use the star operator!
Another cool column hack is how if a column name belongs to only one of your tables in your JOIN, you don’t even need to use an alias. SQL Server is smart enough to recognize which table the column belongs to.
Here’s an example:
For example, there simply isn’t an OrderID column in the Customers table, so SQL Server understands the column must be from the Orders table. Therefore, no alias is required. The same thing is true for the OrderDate column.
The same thing is true for our columns from the Customers table. There is no FirstName column in the Orders table, so this column must belong to the Customers table. No alias needed!
But we know there is at least one column that has the same name in both tables. That column is CustID.
Would the following query work?
Nope! If you run this, you get this error message:
Ambiguous column name ‘CustID’.
SQL Server doesn’t know which CustID you mean, because there are two. In that case, we need to use an alias:
It’s the same reason we need to use aliases in the ON clause. If we didn’t use aliases here, the ON clause would look like this:
ON CustID = CustID
LOL, what?
If the two columns were named differently, we could get away with this. But they’re not, so we can’t!
Tip # 2: For an INNER JOIN, the order of your tables in your query does not matter
This whole time, we have been using the Orders table in the FROM clause and the Customers table in the INNER JOIN clause.
But we can flip it:
Remember, matching rows are matching rows. We only pull column information where the CustID value has a match in both tables.
Tip # 3: The word ‘INNER‘ is optional
If you just type the word ‘JOIN‘ by itself, SQL Server knows you mean an INNER JOIN. Check it out:
Personally, I like to be very explicit, so I will usually include the word ‘INNER‘ even though I don’t need to.
Tip # 4: It’s possible to perform an UPDATE statement with a JOIN
Instead of doing a simple SELECT statement with a JOIN to simply return data, we can instead write an UPDATE statement with a JOIN to modify data.
Take a look at the full tutorial to learn how:
UPDATE statement with JOIN: How it’s done
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your FREE Ebook:
FREE Ebook on SQL Server JOIN Operations!
This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!
Be sure to check out my full tutorial on LEFT OUTER JOINs. These are another very common type of join you will see in your career, so it’s important you understand the difference between it and an INNER JOIN:
SQL Server LEFT OUTER JOIN: The Ultimate Guide for Beginners
Also, I put together a tutorial to answer some of the most beginner-level questions you might ask if you are just starting out with SQL Server and are trying to learn about JOINs. Here’s the link:
What’s the difference between JOIN and LEFT JOIN? This and other JOIN questions, answered!
Finally, the INNER JOIN is part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
