A SELF JOIN connects two tables together. Table aliases can be used to make this join, allowing us to avoid repeating the same table name in a single line. If we use the same table name more than once in a single query without using table aliases, we’ll get an error.

When we wish to merge data from several tables within the same database, we need to use a SELF JOIN. Converting a hierarchical organisation to a flat structure is frequently beneficial.

The SELF JOIN is demonstrated in the following syntax:

SELECT column_lists    
FROM table1 AS T1, table1 AS T2    
WHERE join_conditions;

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;