How to implement Association query in MySQL
This article will explain in detail how to achieve related queries in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.
MySQL
Association query
Previously, we introduced single-table queries (that is, only getting data from one table), but in practical application, we will query multiple tables at the same time. Here, we will introduce the use of multi-table association queries.
SQL join is used to query data from two or more tables based on the relationship between the columns in these tables
Pre-knowledge
Primary key (Primary Key): a field that can uniquely identify a record, such as the student ID in the student table and our ID number in our life.
Foreign key (Foreign Key): the primary key that points to another table, such as class ID in the student table. Class ID is the primary key in the class table, but it is the foreign key in the student table.
Primary keys and foreign keys can be specified when creating tables, and they can control the integrity and consistency of your data at the database level.
Test data reference: http://yuguiyang.github.io/2017/09/09/mysql-handbook-01/
Inner join
Inner join can be abbreviated to join, and the result set is an intersection of records that exist in both tables. Refer to the picture above for details.
For example, in the student table, there is a class ID. We want to find the class information in the class table according to the class ID.
Select
*
From
T_student a
-- the table to associate with the query
Join
T_class b
What fields are used to associate the two tables
On
A.c_id = b.c_id
Left join
Left association, with the left table as the primary table, no matter whether the foreign key exists in the right table or not, the data of the left table will exist.
For example, in the student table, there is a record that his class ID is 904, but there is no class information of 904 in the class table.
So, you can't find this record if you use join.
-2. Left join
-- the student table is the main table and contains all student information
Select
*
From
T_student a
Left join
T_class b
On
A.c_id = b.c_id
Right join
Right association, which is similar to making an association, but the right table is the primary table
-3. Right join
-- the class table is the main table, regardless of whether the class has student information or not.
Select
*
From
T_student a
Right join
T_class b
On
A.c_id = b.c_id
Full outer join
Full relevance, mysql does not have full join syntax, we can achieve it by using union
Select
*
From
T_student a
Left join
T_class b
On
A.c_id = b.c_id
UNION
Select
*
From
T_student a
Right join
T_class b
On
A.c_id = b.c_id
Cross join
Cross join is a Cartesian product of two tables.
Select * from t_class a
Cross join t_class b
Order by a.c_id,b.c_id
On how to achieve the association query in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
