Detailed explanation of Mysql self-join query example

This article describes the Mysql self-join query. Share with you for your reference, the details are as follows:

Self-join query

Imagine the following scenario: an e-commerce website wants to classify the products on the site into a hierarchy, with one category having several subcategories, and each subcategory having other subcategories. For example, under the category of digital products, there are laptops, desktops, smartphones, etc.; laptops, desktops, and smartphones can be classified by brand; brands can be classified by price, and so on. Perhaps these classifications will reach a very deep level and present a tree-like structure. So how should this data be represented in the database? We can create two fields in the database to store the id and category name, use a third field to store the id of the subcategory or parent category of the category, and finally query the desired results through self-join.

A self-join query is actually equivalent to a join query, which requires two tables, except that both its left table (parent table) and right table (child table) are itself. When doing a self-join query, you join the table to itself, give the parent table and the child table two different aliases, and then attach the join conditions. Take a look at the following example:

1. Create a data table:

create table tdb_cates(
 id smallint primary key auto_increment,
 cate_name varchar(20) not null,
 parent_id smallint not null
);

Note: cate_name represents the name of the category, and parent_id represents the id of the parent category.

2. Insert data:

insert into tdb_cates(cate_name, parent_id) values('digital products', 0);
insert into tdb_cates(cate_name, parent_id) values('Household Products', 0);
insert into tdb_cates(cate_name, parent_id) values('Notebook', 1);
insert into tdb_cates(cate_name, parent_id) values('smartphone', 1);
insert into tdb_cates(cate_name, parent_id) values('Electrical appliances', 2);
insert into tdb_cates(cate_name, parent_id) values('furniture', 2);
insert into tdb_cates(cate_name, parent_id) values('Refrigerator', 5);
insert into tdb_cates(cate_name, parent_id) values('washing machine', 5);
insert into tdb_cates(cate_name, parent_id) values('Car brand', 0);
insert into tdb_cates(cate_name, parent_id) values('Buick', 9);
insert into tdb_cates(cate_name, parent_id) values('宝马', 9);
insert into tdb_cates(cate_name, parent_id) values('Chevrolet', 9);
insert into tdb_cates(cate_name, parent_id) values('Home Textiles', 0);

Query results:

3. Query all categories and their parent categories: Assume that there are two tables (both are tdb_cates), the left table is the child table, and the right table is the parent table; query the id of the child table, the cate_name of the child table, and the cate_name of the parent table; the join condition is that the parent_id of the child table is equal to the id of the parent table.

Query results:

4. Query all categories and subcategories of categories: Let’s still assume that there are two tables (both are tdb_cates), the left table is the child table, and the right table is the parent table; query the id of the child table, the cate_name of the child table, and the cate_name of the parent table; the connection condition is that the id of the child table is equal to the parent_id of the parent table.

Query results:

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.