How to get the intersection of two tables using MySQL's INNER JOIN function

How to use MySQL's INNER JOIN function to obtain the intersection of two tables

In the MySQL database, the INNER JOIN function is a query operation used to join two or more tables. It can obtain the intersection data of two tables based on the related fields between them. Using the INNER JOIN function can make data query and analysis more flexible and efficient. This article will introduce how to use MySQL's INNER JOIN function to obtain the intersection of two tables, and provide relevant code examples.

First, we create two simple tables, namely "students" and "courses", and insert some sample data:

The code to create the table and insert data is as follows:

-- 创建学生表格
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 插入学生数据
INSERT INTO students (id, name)
VALUES (1, '张三'), (2, '李四'), (3, '王五');

-- 创建课程表格
CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

-- 插入课程数据
INSERT INTO courses (id, course_name)
VALUES (1, '数学'), (2, '英语'), (3, '物理');

Now that we have created two tables, we will use the INNER JOIN function to obtain the intersection data of the two tables. In this example, we will use the "ID" field from the "students" and "courses" tables to make the association.

The following is a code example that uses the INNER JOIN function to query the intersection data of two tables:

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.id = courses.id;

In the above code, we use the INNER JOIN keyword to indicate the connection method, and use ON clause to specify the association conditions between tables. In this example, we associate through "students.id = courses.id", that is, match with the "ID" field shared by the two tables.

After running the above code, we will get the following results:

+-------+-------------+
| name  | course_name |
+-------+-------------+
| 张三  | 数学        |
| 李四  | 英语        |
| 王五  | 物理        |
+-------+-------------+

Through the INNER JOIN function, we successfully obtained the intersection data of the two tables "students" and "courses". As you can see from the results, we get the name of each student and the name of the course they chose.

To summarize, using MySQL’s INNER JOIN function can easily obtain the intersection data between two tables. By specifying association conditions, we can flexibly query and analyze data. Through the sample code in this article, I believe readers have a deeper understanding of how to use the INNER JOIN function to obtain the intersection of two tables.

The above is the detailed content of How to get the intersection of two tables using MySQL's INNER JOIN function. For more information, please follow other related articles on the PHP Chinese website!