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:
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:
NOTE: You can also use another condition instead of WHERE clause according to your requirements.
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:
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.
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.
After executing the above statement, we will get the following example:
