Use MySQL's JOIN function to join tables

Use MySQL's JOIN function to join tables

In MySQL, JOIN is a very common operation, which allows us to join two or more tables based on the associated fields between them. connect. This makes it easy to query and obtain relevant data from multiple tables, improving query efficiency and flexibility. This article will use code examples to demonstrate how to use MySQL's JOIN function to join tables.

First create two sample tables:studentsandscores. Thestudentstable contains basic information about students, including fields such asid,name, andage; thescorestable contains Student performance information, including fields such asid,subject, andscore. The two tables are related through the student'sidfield.

-- 创建students表 CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); -- 插入示例数据 INSERT INTO students (id, name, age) VALUES (1, 'Alice', 18), (2, 'Bob', 20), (3, 'Charlie', 19); -- 创建scores表 CREATE TABLE scores ( id INT, subject VARCHAR(50), score INT ); -- 插入示例数据 INSERT INTO scores (id, subject, score) VALUES (1, 'Math', 90), (1, 'English', 85), (2, 'Math', 95), (2, 'English', 80), (3, 'Math', 85), (3, 'English', 90);

1. INNER JOIN

Inner join is the most commonly used connection method. It returns records in two tables that meet the join conditions.

SELECT students.name, scores.subject, scores.score FROM students INNER JOIN scores ON students.id = scores.id;

The above code will return a result set containing student names, subjects and grades.

2. LEFT JOIN

Left join returns all records in the left table and records in the right table that meet the connection conditions, if there are no matching records in the right table , then the value of the relevant field in the right table in the result is NULL.

SELECT students.name, scores.score FROM students LEFT JOIN scores ON students.id = scores.id;

The above code will return a result set containing student names and grades. If a student does not have a corresponding score record in thescorestable, then the student's score field value is NULL.

3. RIGHT JOIN

Right join returns all records in the right table and records in the left table that meet the join conditions, if there are no matching records in the left table , then the value of the relevant field in the left table in the result is NULL.

SELECT students.name, scores.subject, scores.score FROM students RIGHT JOIN scores ON students.id = scores.id;

The above code will return a result set containing student names, subjects and grades. If a subject does not have a corresponding student record in thestudentstable, then the subject field value is NULL.

4. Full join (FULL JOIN)

Full join returns all records in the left table and right table. If there are no matching records in a table, then the results are related The value of the field is NULL.

SELECT students.name, scores.subject, scores.score FROM students FULL JOIN scores ON students.id = scores.id;

The above code will return a result set containing student names, subjects and grades. If a student does not have a corresponding score record in thescorestable, or a certain subject does not have a corresponding student record in thestudentstable, then the corresponding field value is NULL.

Through the above examples, we can see that the JOIN function of MySQL can be used to flexibly connect tables, thereby conveniently querying and obtaining relevant data. This kind of operation is very common in actual database applications, especially when the data relationship is complex, it can greatly improve the efficiency and flexibility of queries.

It should be noted that the JOIN operation may have a certain impact on database performance. Therefore, when using JOIN, optimization should be carried out according to the actual situation, and the association between tables should be designed as well as possible.

The above is the detailed content of Use MySQL's JOIN function to join tables. For more information, please follow other related articles on the PHP Chinese website!