Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL Server - Get Started
  • Install SQL Server
  • SQL Server Management Studio
  • SQL Server - Windows Authentication
  • SQL Server - Authentication
  • SQL Server - Create New User
  • SQL Server - GRANT/REVOKE Permissions to User
  • SQL Server - Data Types
  • SQL Server - Naming Conventions
  • SQL Server - CREATE Database
  • SQL Server - CREATE Table
  • Add Columns
  • Identity Column
  • Rename Column, Table
  • Drop Columns
  • SQL Server - Schema
  • SQL Server - Tables Relations
  • SQL Server - Primary Keys
  • Modify/Delete Primary Keys
  • SQL Server - Foreign Keys
  • Modify/Delete Foreign Keys
  • SQL Server - Check Constraints
  • SQL Server - Unique Constraints
  • SQL Server - Views
  • Modify/Delete Views
  • SQL Server - Functions
  • SQL Server - Stored Procedures
  • Stored Procedure Parameters
  • SQL Server - Indexes
  • Non-clustered Indexes
  • Modify/Delete Indexes
  • SQL Server - Triggers
  • DDL Triggers
  • LOGON Triggers
  • Enable/Disable Triggers
  • Modify/Delete Triggers
  • SQL Server - Sequence
  • SQL Server - Synonyms
  • SQL Server - IF ELSE Statement
  • SQL Server - Loops
  • SQL Server - Insert Data
  • SQL Server - Update Data
  • SQL Server - Delete Data
  • SQL Server - Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL Server - Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Self Join
  • Dynamic SQL
  • Built-in Functions
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

Self-Join in SQL Server

In SQL Server, the self-join is like a regular join, but it joins a table to itself. Similar to any other join, a self-join requires at least two tables. But instead of adding a different table to the join, you add another instance of the same table. It is a join between two copies of the same table. Self-join is mainly used for querying the hierarchical data stored in a single table.

There is no Self Join keyword. You write a normal join where both the tables involved in the join are the same.

The following is the syntax of the self-join query.

Syntax: Self-join
SELECT a.column1, b.column2
FROM table1 a, table1 b
WHERE condition;

In the self-join, you have to specify the table alias to both the instance of a table; else you will get an error.

Consider the following Employee table that contains EmployeeID and ManagerID columns. The ManagerID column value will be any of the EmployeeID. Thus, we can find the manager's information in the same table.

The Employee table contains the following data.

In the above table, EmployeeID 7 contains ManagerID 1 as his manager. EmployeeID 1 contains NULL in the ManagerID column as he doesn't have a manager.

The following self-join query gets the managers of all the employees.

Example: Self-join
SELECT emp.FirstName + ',' + emp.LastName as Employee, 
mgr.FirstName + ',' + mgr.LastName as Manager
FROM Employee emp
INNER JOIN Employee mgr ON emp.ManagerID = mgr.EmployeeID;

The above query would display the following result in SQL Server.

The above result displays all employees having a manager along with their manager name. Employees who do not have a manager (ManagerID column is NULL) are not displayed.

You can tweak the above query to list all employees even if they do not have a manager using the LEFT JOIN query.

Example: Left-join
SELECT emp.FirstName + ',' + emp.LastName as Employee,
mgr.FirstName + ',' + mgr.LastName as Manager
FROM Employee emp
LEFT JOIN Employee mgr ON emp.ManagerID = mgr.EmployeeID

The above query would display the following result.

TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.