Welcome to Appsloveworld Technologies, In this session, we’ll talk about self join, which is nothing but joining a table with itself, will also talk about self joined classification and Purpose of Self-Join.
Most of the people think self-join is a different type of join altogether, which is not true. Self-join can be classified as inner, outer, cross join depending on how you write the self join.
Before continuing with the session. I strongly recommend reading SQL join tutorial first. In this post, we have already discussed how to join different tables.
What is Self Join?
If you look at the table here that we have, it has got three columns, EmpId, EmployeeName, and Manager_Id.
Now let’s say I want you to write a query which gives me an output like this. I want the employee’s name as well as his manager’s name.
If you need SQL script to create these two tables, you can find the script at end of the post.
Now if you look at the table on the left-hand side, if you look at Michael, Michael’s manager ID is 5 and you take this 5 and look up in the employee ID column, 5 is Grayson.
That’s means Michael’s manager is Grayson and along the same lines, if you look at Jackson, his manager ID 6 and you take this 6 and look up in the employee ID column 6 is Gabriel.
so Jackson’s manager is Gabriel. So if you look at the situation here, we are using the Manager_Id column and looking up in the employee ID column within the same table, which means we are referencing the same table.
You can say that i want a sql query to get the employee name and their manager name from the same table.
If we want this kind of output like employee name and manager’s name, there is a need to join this employee table with itself.
Now you might be wondering how can we join the same table with itself. if you look at below query, we have done exactly the same thing.
How to get the employees with their managers from the same table?
Select Emp.EmployeeName as Employee, Mng.EmployeeName as Manager from TblEmployeeManager Emp Left Join TblEmployeeManager Mng On Emp.Manager_Id = Mng.EmpId
So if you look at the output, we want the employee name and the manager’s name. So select employee name, manager name will come to those in the column list, in the select clause.
If you look at the form clause, we are saying TblEmployeeManager and we are giving it a table alias Emp and we are saying, join this table left join, we are doing a left join with TblEmployeeManager again.it can be inner join left join whatever.
So select whatever columns you want from TblEmployeeManager give it an alias emp join with the same table TblEmployeeManager and give it another alias Mng, emp for the employee, mng for the manager.
So you can assume now we have two instances of this table and one for employee and then another table like the manager Table.both of the structures will be identical and you’re basically referencing the same table.
And then what you are doing is in the employee table, take the managerId column in the employee table, take the manager Id column and look up the employee ID in the manager’s table, which is nothing but another copy of the same table. and then from the employee table, which is nothing but emp I want the name which is nothing but the employee name.
So I’m giving it alias as an employee and from the manager’s table, I’m getting the name which is nothing but the manager’s name.So that’s how we get this output.
But if you look at the join here, we are joining with the same table. So this is nothing but a self-join.
So when we execute this query, we get the output as we expect.
Get List all employee’s names and their managers by manager name using an inner join
As we know that inner join, will only give the matching rows between the two tables.When we were using left join, we were getting Anthony’s record as well and we get 9 rows.
but when I change it to an inner join.
Select Emp.EmployeeName as Employee, Mng.EmployeeName as Manager from TblEmployeeManager Emp Inner Join TblEmployeeManager Mng On Emp.Manager_Id = Mng.EmpId
Now, when I execute this one, look at what’s going to happen. I only get 8 rows. what happens to the record. It’s not retrieved anymore. Why? Because if you look at Anthony’s record, the managerId does not match with any of the employee Id.
There is no matching record. And if you remember, the inner join will only give you the matching records between the two tables.
Now, you might be wondering where are two tables here? But remember, in the join, we are joining this table with itself. So we are treating a single table like two tables.So you can call this as an inner self join.
why is it called a self join? because you are joining the same table with itself and you are doing an inner join, inner self join.
So along the same lines, you can do it right outer self join and full outer self join. So a self-join is not a different type of join all together. It can be classified under any type of join,inner-outer,left, right, and cross join as well.
Cross join in SQL example employee manager
Select Emp.EmployeeName as Employee, Mng.EmployeeName as Manager from TblEmployeeManager Emp Cross Join TblEmployeeManager Mng
So if you want to convert this into a cross, join the first thing you have to do, get rid of the on the clause, and then convert inner join into the cross join. That’s about it. Since we are joining the same table with itself, we call it, you know, a self join .But since we are using cross join, it’s called Let’s Cross Self Join.
OK, now remember in the TblEmployeeManager, we’ve got 9 rows. And if you remember from the definition of the cross, join when you do across, join between two tables, you’ll get the Cartesian product i.e 9*9=81 rows as shown in image.
Let’s say we want to write a SQL query to display all employees under each manager. Let’s say we need to get all employees starting from ManagerId=7 i.e from the super manager.
;WITH QueryResult (Manager_Id, EmpId, LEVEL) AS ( SELECT DISTINCT Manager_Id, EmpId AS EmployeeId, 0 AS LEVEL FROM TblEmployeeManager UNION ALL SELECT tbl.Manager_Id, tbl.EmpId, LEVEL + 1 AS LEVEL FROM TblEmployeeManager tbl INNER JOIN QueryResult T ON tbl.Manager_Id = T.EmpId WHERE tbl.Manager_Id <> tbl.EmpId ) SELECT DISTINCT EmpId, Manager_Id, LEVEL FROM QueryResult ORDER BY Manager_Id
Sql Table Script
--Self join Table CREATE TABLE [dbo].[TblEmployeeManager]( [EmpId] [int] IDENTITY(1,1) NOT NULL, [EmployeeName] [nvarchar](max) NULL, [Manager_Id] [int] NULL, CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED ( [EmpId] ASC ) ) GO SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7) GO
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?