Using Cross Joins
Try reloading this page, or reviewing your browser settings
This video explains how to use Self Joins to join columns from within a single table.
- Inner Joins
- Multiple Tables
- Self Joins
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
Welcome. In this video segment, we’re going to discuss using self joins. A self join is used when you need to compare columns that are within the same table. So we’re not connecting two separate tables. We’re going to connect a table back to itself. That’s why it’s called a self join.
For example, we have a column named Employee ID. And we have, in that same employees table, a column name Manager ID. So if I wanted to see who employee ID number five reported to– Bucky Denton– I could see that his Manager ID is employee number three. But who is employee number three? If I look up, I could see that employee number three is Becky Lightyear. So to get this information into a single results set, I would need to connect or join the Employee ID field to the Manager ID field from within the same table.
So here is the sample code on how to connect to two tables. You can see I’m selecting the Employee ID, First Name, and Last Name from the E table or the Employees Table, hr.employees. And I’m retrieving the Last Name field from the table that’s being aliased as M.
When I look at the two tables that I’m joining, they are both the Employees Table. hr.employees as E inner join hr.employees as M. So it is the same table that I am joining to itself. But I’m selecting the Manager ID field from the table that was aliased as E and connecting it to the Employee ID field for the table that was aliased as Manager. Let’s see this in our code.
So as you can see here, I’m starting off with a basic select statement, selecting Employee ID, First Name, Last Name, and Manager ID from the Employees Table. And our results set shows eight employees. But as we mentioned, I want to join this table, the Employees Table. I want to alias this one as E. And we’re going to create an inner join back to the same table, hr.employees.
And to separate them, we’re going to alias this one as M for managers. And I want to connect this table back to itself. For the table aliased as E, I’m going to grab the Manager ID field and make that equal to the Employee ID field from the table that was aliased as Managers.
Now, you may notice that my columns have a red squiggly underneath their names. That’s because these columns are actually in both tables that I’m joining, or specifically that same table. So I need to identify which table I want these fields. So I’ll say, the Employee ID I want from the Employee table, or the table that was aliased as E.
And then the First Name and then the Last Name– these are all coming from the table that I’m aliasing as E. And instead of Manager ID, I want to get the last name of the manager. So I’m going to grab from the table that is the table being aliased as Manager and select the Last Name.
So now when I execute my code, you can see that employee number five, Bucky Denton, is reporting to Becky Lightyear. Now, you may notice I do not have Employee ID number one. That was because employee ID number one did not report to any manager. Jackson Johnson was the owner of the company.
If I did want to see all the employees whether they had a manager or not, instead of using an inner join that would just show values that matched, I would use a LEFT OUTER JOIN to bring everything from the table aliased as E, including Jackson Johnson who did not have a manager.
So in this video segment, we have discussed how to use self joins.