MySQL SELF JOIN

This join links table with itself. The inner join, a self join, a right join, and a cross join are connected with two or more two tables. But, the "self join" connects with itself. The self join returns the record of the single table. It uses for structured data or hierarchical table format. For example, if you want to compare rows and columns in the table, use self join.

Syntax

The "self join" syntax shows below.

SELECT Column1 data type constraint,
Column2 data type constraint,
FROM table AS id1 
INNER JOIN table AS id2 condition; 

The "self join" with the "WHERE" clause syntax shows below.

SELECT Column1 data type constraint,
Column2 data type constraint,
FROM table id1, table id2 
WHERE id1.Column1 = id2.Column1; 

The "self join" with the "inner join" syntax shows below.

SELECT column list FROM table.id1 INNER JOIN table.id2 condition; 

The "self join" with the "self join" syntax shows below.

SELECT column list FROM table.id1 SELF JOIN table.id2 condition; 

Examples of the MySQL self join.

1) Example: the self joins with a single table example shows below.

Execute the below query to join two tables using the "SELF JOIN" query. This query uses the ON and WHERE clauses with SELF JOIN.

mysql> select
concat (d.department_id, ', ',  d.department_name) AS department,
concat (s. department_id, ', ', s. department_name) AS subject
FROM department d
INNER JOIN department s;

OUTPUT

The above output image shows the required columns of one table. The department and subject are two ids of the single table. The "department_name" and "department_id" columns show in the department table with a different id.

2) Example: the self join with inner join example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions.

mysql> select
concat (d.department_id, ', ',  d.department_name) AS department,
concat (s. department_id, ', ', s. department_name) AS subject
FROM department d
INNER JOIN department s
ORDER BY department;

OUTPUT

The above output image shows the required columns from a single table. The "department_name" and "department_id" columns show in the department table with a different id.

3) Example: the self join with the "on" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions.

mysql> select
    -> concat (d.department_id, ', ',  d.department_name) AS department,
    -> concat (s. department_id, ', ', s. department_name) AS subject
    -> FROM department d
    -> INNER JOIN department s
   -> ON d.department_id = s. department_id
    -> ORDER BY department DESC;

OUTPUT

The above output image shows the required columns of one table. The department and subject are two ids of the single table. The "department_name" and "department_id" columns show in the department table with a different id.

4) Example: the self-join with the left join example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions.

mysql> select
concat (d.department_id, ', ',  d.department_name) AS department,
concat (s. department_id, ', ', s. department_name) AS subject
FROM department d
LEFT JOIN department s
ON d.department_id = s. department_id
ORDER BY department DESC;

OUTPUT

The above output image shows the required columns of one table. The "department_name" and "department_id" columns contain in the department table with a different id.  The department and subject are two ids of the single table. The order by clause uses descending order for the department column.

5) Example: the self-join with the "WHERE" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. The "WHERE" clause uses for restriction and limitations on the table data.

mysql> select
concat (d.department_id, ', ',  d.department_name) AS department,
concat (s. department_id, ', ', s. department_name) AS subject
FROM department d
INNER JOIN department s
ON d.department_id = s. department_id
WHERE d.department_id < 5;

OUTPUT

This result displays the required columns of a single table. The "department_name" and "department_id" columns contain in the department table with a different id.  The department and subject are two ids of the single table. 6) Example: the self-join with the "LIMIT" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. The "LIMIT" clause uses for restriction and limitations on the table data.

mysql> select
concat (d.department_id, ', ',  d.department_name) AS department,
concat (s. department_id, ', ', s. department_name) AS subject
FROM department d
INNER JOIN department s
ON d.department_id = s. department_id
WHERE d.department_id < 5
LIMIT 2;

OUTPUT

The above output image shows the required columns of one table. The "department_name" and "department_id" columns show in the department table with a different id.  The department and subject are two ids of the single table. The "where" clause uses the "less than" five departments with two rows limit.