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: students and scores. The students table contains basic information about students, including fields such as id, name, and age; the scores table contains Student performance information, including fields such as id, subject, and score. The two tables are related through the student's id field.

-- 创建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 the scores table, 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 the students table, 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 the scores table, or a certain subject does not have a corresponding student record in the students table, 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!