This tutorial will show you how to use the MySQL self-join to merge a table with itself and create customized data.
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:
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)
CREATE SCHEMA self;
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
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.
The output is shown below:
Self Join using Left Join
The example query below explains how we can use self join with left join.
The output result is below:
This guide walked you through how you can use MySQL self join to join a table with itself.
Thank you for reading.