SQL SELF JOIN is a normal join which joins a table to itself.
Note: SQL SELF JOIN can be any kind of joins: SQL INNER JOIN, SQL LEFT JOIN, SQL RIGHT JOIN, SQL FULL JOIN. And that SQL SELF JOIN itself is not an SQL keyword.
SQL SELF JOIN Syntax
SELECT a.Column1, a.Column2, ... , b.Column3, b.Column4, ... FROM Table1 a INNER JOIN Table1 b ON a.Column1 = b.Column2
SQL SELF JOIN Example
Table: Employees
EmployeeId | FirstName | LastName | Department | Salary | ManagerID |
---|---|---|---|---|---|
203 | Mazojys | Fxoj | Finance | 78000 | |
204 | Jozzh | Lnanyo | Finance | 45800 | 203 |
205 | Syllauu | Dfaafk | Finance | 57000 | 203 |
302 | Gecrrcc | Srlkrt | Development | 62000 | 305 |
303 | Tyshub | Yunshee | Development | 58000 | 305 |
305 | Hnnval | Keevvs | Development | 79000 |
Select all the employees and his/her manager, if has any:
SELECT a.FirstName , a.LastName , b.LastName AS Manager FROM Employees a LEFT JOIN Employees b ON a.ManagerID = b.EmployeeId
The result will look like:
FirstName | LastName | Manager |
---|---|---|
Mazojys | Fxoj | |
Jozzh | Lnanyo | Fxoj |
Syllauu | Dfaafk | Fxoj |
Gecrrcc | Srlkrt | Keevvs |
Tyshub | Yunshee | Keevvs |
Hnnval | Keevvs |
Select all the employees at the same department with Mazojys Fxoj:
SELECT a.FirstName , a.LastName , a.Department FROM Employees a INNER JOIN Employees b ON a.Department= b.Department WHERE b.FirstName = 'Mazojys' AND b.LastName = 'Fxoj'
The result will look like:
FirstName | LastName | Department |
---|---|---|
Mazojys | Fxoj | Finance |
Jozzh | Lnanyo | Finance |
Syllauu | Dfaafk | Finance |