SQL Self Join Explained: Manager-Employee Hierarchy | T-SQL Course Part 14 скачать в хорошем качестве

SQL Self Join Explained: Manager-Employee Hierarchy | T-SQL Course Part 14

How do you join a table to itself? In this tutorial, we master the Self Join in SQL Server. This is a vital skill for dealing with hierarchical data, such as organizational charts where employees and managers are stored in the same table. What you will learn in this session: What is a Self Join? Understanding when and why a table needs to reference itself. The Manager-Employee Logic: How to map a ManagerID back to an EmployeeID within the same table. Table Aliasing is Mandatory: Why you must give the table two different names (like 'e' for Employee and 'm' for Manager) to make this work. Handling the Big Boss: Using LEFT OUTER JOIN so that top-level managers (with NULL MgrID) aren't excluded from your report. Data Formatting: Using ISNULL to replace empty manager names with a professional "No Manager" label. T-SQL PRACTICAL SCRIPT -- 1. Create the Employee table with a Manager ID reference CREATE TABLE [dbo].[Employee]( [EmpID] [int] NULL, [EmpName] [varchar](50) NULL, [MgrID] [int] NULL ) -- 2. Insert Hierarchical Data insert into Employee(EmpID,EmpName) values(1,'ashish') -- Top Manager insert into Employee(EmpID,EmpName,MgrID) values(2,'arun',1),(3,'naveen',1),(4,'sanjay',3),(5,'ankit',3),(6,'rajesh',4) -- 3. THE SELF JOIN -- We treat 'e' as the Employee list and 'm' as the Manager list select e.EmpID, e.EmpName, isnull(m.EmpName, 'No Manager') as ManagerName from employee e left outer join employee m on e.MgrID = m.EmpID #SQLServer #TSQL #SelfJoin #SQLHierarchy #DatabaseDesign #LearnSQL #DataAnalysis #SQLTutorial #AdvancedSQL