A SELF JOIN is used to join a table with itself. This join can be performed using table aliases, which allow us to avoid repeating the same table name in a single sentence. It will throw an error if we use the same table name more than once in a single query without using table aliases.
A SELF JOIN is required when we want to combine data with other data in the same table itself. It is often very useful to convert a hierarchical structure to a flat structure.
The following syntax illustrates the SELF JOIN:
SELECT column_lists
FROM table1 AS T1, table1 AS T2
WHERE join_conditions;
Example
Suppose we have a table 'Student' having the following data:
SQL Interview Questions and Answers
If we want to get retrieve the student_id and name from the table where student_id is equal, and course_id is not equal, it can be done by using the self-join:
SELECT s1.student_id, s1.name
FROM student AS s1, student s2
WHERE s1.student_id=s2.student_id
AND s1.course_id<>s2.course_id;
Here is the result:
