When I was first learning how to query databases using T-SQL, I had some very basic questions about the different JOIN operations.
I had questions like:
“What’s the difference between a JOIN and a LEFT JOIN?”
Or
“What’s the difference between a regular JOIN and an INNER JOIN?”
Or even
“What’s the difference between a LEFT JOIN and a LEFT OUTER JOIN?”
These are great questions you should be asking when you are first learning SQL. You will likely see every type of JOIN operation in your career as a database professional, so it’s important to understand each one and how they work.
This tutorial is dedicated to answering these basic JOIN questions.
We’ll discuss these questions:
- The difference between a regular JOIN and an INNER JOIN
- What’s the difference between a JOIN and a LEFT JOIN?
- The difference between a LEFT JOIN and a LEFT OUTER JOIN
- Is there such a thing as a LEFT INNER JOIN or RIGHT INNER JOIN?
- Do we really need the RIGHT JOIN operation?
Also, check out this FREE Ebook that discusses everything you need to know about the different JOIN operations in SQL Server:
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!
Starting from the top,
1. The difference between a regular JOIN and an INNER JOIN
This first one is easy: There is no difference between a JOIN and an INNER JOIN. They are referring to the same thing.
When doing an INNER JOIN, the word ‘INNER‘ is optional. If you just use the word ‘JOIN‘ by itself, SQL Server knows you mean an inner join.
I mention this and much more in my full tutorial on the INNER JOIN operator. You’ll learn everything you need to know in that tutorial. Check it out here:
SQL Server INNER JOIN operator: The Ultimate Guide for Beginners
2. What’s the difference between a JOIN and a LEFT JOIN?
The JOIN operation (or INNER JOIN, as we now know), is meant to pull rows that have a match in both tables. The LEFT JOIN, on the other hand, is meant to pull all rows from the left table, and only matching rows from the right table.
There is a helpful diagram you will likely encounter when understanding the LEFT JOIN. Here it is:
The LEFT JOIN will just pull all rows from the left table, regardless of if there is a matching row in the right table. But from the right table, we only pull a row if it has a matching row in the left table.
Let’s think of a quick example. Let’s say you have a table called Products with a ProdID column:
Then we have a table called Orders. In this table, each row represents a unique Order that was placed for our business. In each row, we have a reference to a ProdID to identify what product was purchased:
If we wanted to see the Product Names for all products that have been ordered, that would be a simple INNER JOIN operation, where we match on the Product ID:
Nice, but that’s an INNER JOIN.
What if you want to know what Products haven’t been purchased by anyone? In that case, you would want to see all Products, regardless of if there is a match in the Orders table.
The LEFT JOIN:
We can see that information using a LEFT JOIN operation, making our Products table the left table. This way, we see all products in the Products table regardless of if they have any orders associated with them.
So if we change our query above to a LEFT JOIN, order-less products will have a ProdID and a ProdName, but no OrderID.
Basically like so:
So the Calendar Rack and Plaque products have never been ordered because they don’t have an OrderID.
Check out the full tutorial on the LEFT JOIN operation where I answer this question and many more:
SQL Server LEFT JOIN: The Ultimate Guide for Beginners
3. The difference between a LEFT JOIN and a LEFT OUTER JOIN
This is another easy one: There is no difference between a LEFT JOIN and a LEFT OUTER JOIN. The word ‘OUTER‘ is optional. In the real world, you will likely see it written as ‘LEFT JOIN‘ with the word ‘OUTER‘ omitted.
I understand the confusion one has when seeing many instances of a LEFT JOIN being used, then suddenly you find a tutorial where someone chose to throw the word ‘OUTER‘ in there and thoroughly confuse you.
Now you know those people just like to be explicit, I guess.
While we’re at it, a RIGHT JOIN and a RIGHT OUTER JOIN are also the same thing. The word ‘OUTER‘ is optional!
4. Is there such a thing as a LEFT INNER JOIN or RIGHT INNER JOIN?
Nope, that’s not a thing.
I guess that’s why the word ‘OUTER‘ is optional. SQL knows your left and right joins are OUTER because that’s all they can be.
Moving on….
5. Do we really need the RIGHT JOIN operation?
Not really.
The thing about a RIGHT JOIN is that you can accomplish the same thing by using a LEFT JOIN and just flipping the tables around.
Remember this LEFT JOIN query we did a minute ago:
Notice if we flip the tables around, and use a RIGHT JOIN instead, we get the same results:
The same results!
Most people just stick with using LEFT JOIN, but I suppose if you couldn’t rearrange your tables (for whatever reason) you could RIGHT JOIN your way out of the situation.
Again, check out my full tutorial for more factoids about joins:
SQL Server LEFT JOIN: The Ultimate Guide for Beginners
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, make sure you 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!
Also, do you know how to write a JOIN in an UPDATE statement? Click the link to find out!
Be sure to check out my tutorial on the INNER JOIN. This tutorial serves as the introduction to joins and why we need them. That tutorial contains several tips and tricks you should know when writing joins. Here’s the link:
SQL Server INNER JOIN: The Ultimate Guide for Beginners
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 don’t hesitate to leave a comment. Or better yet, send me an email!
