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 to combine rows based on the conditions specified.
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 data:
INSERT INTO users(first_name, email, payment_id, subscription) VALUES ("Valerie G. Phillip", "ValerieGPhillip@teleworm.us", 10001, 1), ("Sean R. Storie", "SeanRStorie@rhyta.com", 10005, 2), ("Bobby S. Newsome", "BobbySNewsome@armyspy.com", 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 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.
