Hey there! In this video, you'll learn all about the concept of a Self Join:
what it is, why you'll need it, and see some examples.
If you've never heard of a self join before, allow me to explain.
A "self join" is the practice of joining a table to itself.
Wait, how do you do that? Why would you want to do that? What's the point?
When you write queries that select data from two or more tables,
you use joins to connect them on common fields. I've got another
video that explains the concept of a join if you wanted to go more in-depth on that.
In some situations, you may need to get data from the same table more than once.
Why would you want to do that? Let's look at an example.
There are two common examples of a table design where self joins are often used.
The first is employees and managers. Let's say we have this table here,
called employee. If you've done any research into joins or seen any Oracle documentation or blogs,
you'll notice that this employee example is used a lot. It's a good example.
The example in this video, the concept of a self join, and the SQL,
will work on many different databases, such as Oracle, SQL Server, PostgreSQL, and MySQL.
So, we have an employee table that has an ID and a name.
How do we store an employee's manager? We could create a second table called manager,
where a manager can have one or more employees.
But then what about the managers of those managers?
We could create another table for each level of manager.
But that means that the structure isn't very flexible and our queries can get complex.
If we think about the concept of a manager, we may realise a manager is also an employee. So,
we can store the manager's details in the employee table, in the same way as other employees.
How do we capture the manager of an employee?
We can add a new column to the table called manager_id.
This is a foreign key. It refers to another record in the same table.
This is a strange concept, but it works. It's perfectly valid
to have a foreign key refer to the same table.
You probably don't want to refer to the same row, but you can refer to other rows in the table.
For example, employee ID 1 has a manager ID of 2, which refers to employee ID 2 who has
the name of Michael. Michael can have his own manager, which is another record in the table.
So this is all well and good, but what does it have to do with a self join?
Well, earlier in the video I mentioned that a self join is the practice of joining a table to itself.
Let's see an example of this.
Now, let's say you wanted to list all of the employee names in the table,
along with the names of their manager. What would that query look like?
We can start by selecting from the employee table. We can select the employee ID and the name.
The results look like this.
What about their manager? We know the manager ID, so we can add that to our select clause.
Here are the results.
But we want to see the name of the manager. How can we do that?
The manager is actually a different employee record. If these were in two tables, we would
achieve this by adding a JOIN clause, adding the second table, and specifying the common fields.
A self join is done in the same way.
We add an INNER JOIN to the query, and the other table we join to is also the employee
table. We've specified the same table here twice. Now, we specify the columns.
We want the manager ID in one row to match the employee ID in another row.
We say, ON employee.manager_id = employee.id.
But if we add this, the database won't know what we mean.
We have added the employee table twice, and we won't get the results we want.
How can we specify which table we want in the JOIN clause?
We add table aliases. We can specify one of the employee tables is the employee,
and the other employee table is the manager.
We can do this by adding an "e" or some other short name after the first employee table,
then an "m" after the second table. E stands for employee and M stands for manager.
We can use the word AS here if we like, to specify it is an alias.
It's optional and for table aliases it often comes down to personal preference.
Now, in the join clause, we can clarify our criteria to say ON e.manager_id = m.employee_id.
This should make it clearer. We want the employee's manager ID
to match the manager's employee ID.
After we've added the table aliases, we can add them to the select clause.
Add in the letter "e" before each column here.
Now we have the information for the employee,
because we've got the letter "e" before each column.
What about the manager name?
We've got the employee table with an alias of "M" that represents the manager.
We can show the manager name by adding a column in the select clause,
starting with the table alias of M and then the column name,
such as emp_name. This should show the name of the manager for that employee.
Now let's run the query.
We can see the results here.
We can see each of the employees, their ID, name, and the manager's ID and name. This is done using
a self-join, which is adding the same table into the query twice, and using an alias for each one.
You may have noticed that the employee ID of 5 is not in the list.
We'll explain why this is later in the lesson.
There are two columns with the name of emp_name, as that's what they are called in the table.
This is a little confusing in the output. We can change the name of
the column in the output by using a column alias.
Let's add a column alias of manager_name to the name field that comes from the manager record.
We do this by specifying AS then the manager_name.
Once again, the AS keyword is optional, but I think it's better to add to column aliases
so it's clear that an alias is used and you haven't just forgotten a comma or something.
Here are the results with those changes. We can see that the output now has a manager_name column,
which makes it a little easier to understand.
Here's what the table would look like in an Entity Relationship Diagram.
We can see that there is a relationship line that joins to itself.
This is how we can tell on the diagram that a self-join can be used.
There's another common example that can be used for self joins, and that is product categories.
In eCommerce sites, such as Amazon, products are often grouped into categories.
These categories can have multiple levels of subcategories.
Instead of having 3 or 4 or 10 tables for categories, we can have a single table that
stores categories. We can then have an ID in the table that refers to the parent category ID,
so we know what category each row sits under.
Allowing developers to use a self join by designing our database tables this way
means that we reduce repetition in our tables, write simpler queries,
and can make our data easier to understand.
One final thing to mention is that in our earlier query we used an Inner Join.
This will join all records where there is a match on both sides.
But, sometimes a record won't have a matching record in the table. An
employee may have a null value for manager_id if they are the CEO.
A category may have a null value for the parent category ID if it is the top-level category.
If you use an inner join, then these top-level rows won't be shown.
If you want these rows to be shown, we need to use an outer join.
We can use a left or a right outer join, depending on how the query is written.
In this example, if we want to see all of the employees and their managers,
even if there is no manager such as the employee Claire, we use employee LEFT JOIN manager.
This means all employees are shown, and either their manager or a NULL value is shown.
The results of this left join query are shown here. We can see a slight difference,
but that's the impact of using an outer join, which can be either a left or a right outer join.
And that brings us to the end of this video on the Self Join concept.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit
[databasestar.com](http://databasestar.com/).
That’s where I share my best database-related content.
Which part of this self-join tutorial was the most helpful for you? Was it the example of writing
the employee and manager query? Or the difference between a left and inner join? Or something else?
Thanks for watching