SQL SELF JOIN

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>