MySQL SELF JOIN

A SELF JOIN is a join that is used to join a table with itself. In the previous sections, we have learned about the joining of the table with the other tables using different JOINS, such as INNER, LEFT, RIGHT, and CROSS JOIN. However, there is a need to combine data with other data in the same table itself. In that case, we use Self Join.

We can perform Self Join using table aliases. The table aliases allow us not to use the same table name twice with a single statement. If we use the same table name more than one time in a single query without table aliases, it will throw an error.

The table aliases enable us to use the temporary name of the table that we are going to use in the query. Let us understand the table aliases with the following explanation.

Suppose we have a table named "student" that is going to use twice in the single query. To aliases the student table, we can write it as:

Select … FROM student AS S1 
INNER JOIN student AS S2;

SELF JOIN Syntax

The syntax of self-join is the same as the syntax of joining two different tables. Here, we use aliases name for tables because both the table name are the same. The following are the syntax of a SELF JOIN in MySQL:

SELECT s1.col_name, s2.col_name...
FROM table1 s1, table1 s2
WHERE s1.common_col_name = s2.common_col_name;

SELF JOIN Example

Let us create a table "student" in a database that contains the following data:

Now, we are going to get all the result (student_id and name) from the table where student_id is equal, and course_id is not equal. Execute the following query to understand the working of self-join in MySQL:

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;

After the successful execution, we will get the following output:

SELF JOIN using INNER JOIN clause

The following example explains how we can use Inner Join with Self Join. This query returns the student id and name when the student_id of both tables is equals, and course_id are not equal.

SELECT  s1.student_id, s1.name
FROM student s1
INNER JOIN student s2
ON s1.student_id=s2.student_id
AND s1.course_id<>s2.course_id
GROUP BY student_id;

After executing the above statement, we will get the following example:

SELF JOIN using LEFT JOIN clause

The following example explains how we can use LEFT Join with Self Join. This query returns the student name as monitor and city when the student_id of both tables are equals.

SELECT (CONCAT(s1.stud_lname, ' ', s2.stud_fname)) AS 'Monitor', s1.city
FROM students s1
LEFT JOIN students s2 ON s1.student_id=s2.student_id
ORDER BY s1.city DESC;

After executing the above statement, we will get the following example: