When to Use MySQL Self Join and Examples

MySQL Self-Join is a type of SQL Join that allows you to join a table to itself. It works by using other join types such as inner or left join clause to combine rows based on the conditions specified.

This tutorial will show you how to use the MySQL self-join to merge a table with itself and create customized data.

Basic Usage

MySQL self-join uses table aliases to ensure you do not repeat the same table more than once in one statement.

NOTE: If you are not familiar with table aliases, consider our other tutorial that explains the concept fully.

The general syntax for using a self-join is similar to one when combining two tables. However, we use table aliases. Consider the query shown below:

SELECT alias1.cols, alias2.cols FROM tbl1 alias1, tbl2 alias2 WHERE [condition]

Example Use Cases

Let’s use examples to understand how to perform MySQL self joins. Suppose you have a database with the following information (See full query below)

DROP SCHEMA IF EXISTS self;
CREATE SCHEMA self;
USE self;
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(255),
email VARCHAR(255),
payment_id INT,
subscription INT
);
INSERT INTO users(first_name, email, payment_id, subscription) VALUES ("Valerie G. Phillip", "[email protected]", 10001, 1), ("Sean R. Storie", "[email protected]", 10005, 2), ("Bobby S. Newsome", "[email protected]", 100010, 5);

We will start with an INNER join and finally a left join.

Self Join using Inner Join

The query below performs an INNER join on the table created above.

SELECT al1.* FROM users al1 INNER JOIN users al2 ON al1.subscription = al2.subscription ORDER BY id DESC;

The output is shown below:

Self Join using Left Join

The example query below explains how we can use self join with left join.

SELECT (CONCAT(al1.first_name, ' -> ', al2.email)) AS details , al1.payment_id FROM users al1 LEFT JOIN users al2 ON al1.id=al2.id;

The output result is below:

Conclusion

This guide walked you through how you can use MySQL self join to join a table with itself. Thank you for reading.