Last Updated on January 18, 2020 by Aram
Having trouble understanding SQL joins? Then you are in the right place. Let me try to demystify this key topic for you.
SQL Joins is one of the most important part in SQL. Understanding them is crucial for any person working on SQL, either the DBA, the business analyst or the software developer.
Looking to it from a developer perspective, you must have a solid understanding of the database systems, even if you were a member of a team which already has a person who is dedicated to work on databases.
As technical person who only wants to focus on writing code you should also be aware of the database structure and how tables are interconnected,
Moreover, they have to understand how to write queries ranging from the simplest select statements to the more complicated ones with multiple joins and nested selects, while making sure to properly optimize (or tune) them for the best performance.
This is particularly important since it helps the developer with understanding the project’s structure and data scheme, therefore they can properly work on the data layer.
In this article, I will shed the light on SQL Joins, since it is a highly important topic. I will explain to you the different types of SQL joins with easy to remember examples.
Let’s take a look at the below Venn diagram (set diagram) that illustrates on a high level and in a very simple way the result of the different SQL joins:
SQL Environment Preparations
Now before jumping into explaining the different types of SQL Joins, we need to prepare our SQL environment with some sample tables and data in order to be able to properly provide easy examples for you to remember.
In this article, I am working on an SQL Server Express edition. You can easily download it from the official Microsoft Download Center.
Once downloaded and installed, you should also download and install the Sql Server Management Studio SSMS.
Once both are installed, you can start your Sql Server Management Studio.
Connect to your localhost database, then from the SQL Server explorer, navigate to Database, and then right click and add Database, name it HR.
I will skip the part of tables creation, I will assume you already know this part.
Within this Database, I have created 2 simple tables: Departments and Employees,
I have filled them with a few rows per each to have a proper example to explain our subject topic, SQL Joins.
Below are the table names and the data rows spread within their columns:
Departments
Employees
Now we have our SQL engine, database, tables and data ready, let’s get started with writing our 5 different SQL Joins to see how the results will differ, based on our previous Venn Diagram:
1. Join or Inner Join
Also referred as the natural Join.
This is the most simple and commonly used SQL Join. You can use inner join to retrieve the matching data between two or more tables, no more no less.
Exact Match.
You can understand this as an intersection between 2 tables, as shown in the above set diagram, the result of the intersection is what you will get as the output of the inner join.
The following is a SQL Join:
|
1 2 3 4 |
select Emp.name, Emp.position, Dep.name from Employees Emp join Departments Dep on Emp.department_id = Dep.Id |
The result from this SQL Join will be the intersection between table Employees and table Departments:
As you can notice above, not all data were returned, only the employees that are assigned to departments were retrieved.
2. Left Join or Left Outer Join
Assume that you have table A and Table B, and you want to do a join between these 2 tables.
Left join means that you want to get all the matching results between table A and B, as well as get all the unmatched (null) results from table A (the left table)
So we if we take the sample SQL as in the inner join example, and just add the left keyword to it, see how this would change the result of the SQL query:
|
1 2 3 4 |
select Emp.name, Emp.position, Dep.name from Employees Emp left join Departments Dep on Emp.department_id = Dep.Id |
Did you notice what happened?
With left join, now we are getting all the data of Employees table, however, since we are projecting the name column from Departments table, some employees are not assigned to Department.
Thus you will see the NULL values under the name column
3. Right Join or Right Outer Join
This join is exactly the same as the Left Join, but inverted.
That’s it; This join means that you want to get all the matching results between table A and B, as well as get all the unmatched (null) results from table B (the right table)
Taking our same example as above, if we add right join, let’s see what will happen:
|
1 2 3 4 |
select Emp.name, Emp.position, Dep.name from Employees Emp right join Departments Dep on Emp.department_id = Dep.Id |
See? the result is exactly as the inner join.
This happened because we directed the join to the right side of the query which is the departments table,
so this is interpreted as ‘get all the results from Departments table, with any other intersection involved that might produce NULL results.
As you can notice from the output data that there is no Department that is not assigned to at least one Employee.
Adding a department
If we want to see a difference in the query, then let’s add a department (HR for example) without assigning it to any employee
Now if we run the right join query again, you will notice that there is a new record showing the HR as a department name, but it has no employee
Of course, you can always switch the right join operands and you will get the exact output of a left join, and vice versa.
4. Outer Join or Full join
This join will return everything from the 2 tables, both matching and non-matching results.
It is the equivalent of a union in the set operations. As you can see from our previous set diagram you will get both A and B along with the intersection between both.
And in our example, you will get results from both Departments and Employees Tables:
|
1 2 3 4 |
select Emp.name, Emp.position, Dep.name from Employees Emp full join Departments Dep on Emp.department_id = Dep.Id |
You can say that the full join is the equivalent of both left and right joins blended together.
5. Self Join
There is no specific self join keyword in SQL.
A self join is a concept of inner joining a table with itself.
This is mainly used when you have a table with records that have hierarchical relationship between its rows. In other simpler words, a table that links to itself through a given key field.
To better understand the self join you can through the following example:
Say you have an Employees table, and inside it there is a manager_id column,
in fact the manager_id is the employee_id, since a manager is also an employee.
So some employees will have a link to another employee who will be managing them, and here where we can use self join to query and extract useful information,
It is like getting the employees lead by a manager,
or to know who is the manager of a particular employee or to find the managers in a specific department.
Let’s see how we can write a self join and how would it result in SQL:
|
1 2 3 4 |
select Emp_1.name as employee_name, Emp_2.name as manager_name, Emp_2.position as manager_position from Employees Emp_1 join Employees Emp_2 on Emp_1.manager_id = Emp_2.id |
And the result will be as the below:
With self join you can extract the data from within the same table, such table structure is very common, so now you are aware of how you can do a join on a one table only using the self join.
Summary
With SQL Joins you can retrieve data from different tables with logical relationships, that would be extremely important for a number solutions ranging from desktop, web and mobile applications to crucial reporting engines.
Such output can lead to a highly important decisions for the system users either they were customers or decision makers within organizations.
Therefore, you must always have a full understanding on the SQL Joins, how to write such queries properly and know which type of SQL join would serve the needed requirements.
I hope this article provided you with adequate knowledge and information about this important topic.
Share this article with your friends and colleagues to spread the knowledge and stay posted for my upcoming articles.
If you work on Asp.Net Core, have you checked my latest article about Deploying Asp.Net Core Web Api on IIS? You might find it interesting.
Bonus
As usual, I would like to share with you this brilliant piece of music:
Moonlight Sonata – The Piano Sonata No. 14 by Ludwig van Beethoven
I hope you will enjoy listening to it while learning SQL Joins.