MySQL合并两张表|极客笔记

MySQL合并两张表

介绍

在MySQL数据库中，有时候需要将两张表合并在一起进行查询或者分析。这个过程可以通过使用JOIN语句来实现。在本篇文章中，我们将详细介绍MySQL中合并两张表的方法和示例代码。

1. INNER JOIN（内连接）

内连接是最常用的连接类型，它会根据连接条件从两个表中返回所有匹配的行。下面是INNER JOIN的语法：

SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.列名 = 表2.列名;

现在，我们举例来解释下INNER JOIN的使用。假设我们有两张表：students和grades。students表中包含学生的基本信息，grades表中包含学生的考试成绩。我们可以通过INNER JOIN来查询学生的基本信息和对应的成绩。

首先，创建并插入数据到students表：

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

INSERT INTO students (id, name, age)
VALUES (1, 'Alice', 18),
       (2, 'Bob', 20),
       (3, 'Charlie', 19);

然后，创建并插入数据到grades表：

CREATE TABLE grades (
  id INT PRIMARY KEY,
  student_id INT,
  subject VARCHAR(100),
  score FLOAT
);

INSERT INTO grades (id, student_id, subject, score)
VALUES (1, 1, 'Math', 90),
       (2, 1, 'English', 85),
       (3, 2, 'Math', 92),
       (4, 2, 'English', 88),
       (5, 3, 'Math', 95);

接下来，我们可以使用INNER JOIN来查询学生的基本信息和对应的成绩。

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

查询结果如下：

id | name    | subject | score
---|---------|---------|------
1  | Alice   | Math    | 90.0
1  | Alice   | English | 85.0
2  | Bob     | Math    | 92.0
2  | Bob     | English | 88.0
3  | Charlie | Math    | 95.0

2. LEFT JOIN（左连接）

左连接会返回左表中所有的行，以及与右表匹配的行。如果右表中没有匹配的行，则结果中右表的列会被填充为NULL。下面是LEFT JOIN的语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名;

继续以上面的示例为基础，我们使用LEFT JOIN来查询学生的基本信息和对应的成绩。

SELECT students.id, students.name, grades.subject, grades.score
FROM students
LEFT JOIN grades
ON students.id = grades.student_id;

查询结果如下：

id | name    | subject | score
---|---------|---------|------
1  | Alice   | Math    | 90.0
1  | Alice   | English | 85.0
2  | Bob     | Math    | 92.0
2  | Bob     | English | 88.0
3  | Charlie | Math    | 95.0
3  | Charlie | NULL    | NULL

需要注意的是，LEFT JOIN返回了左表students中所有的行，而右表grades中没有与学生Charlie匹配的行，所以对应的成绩列被填充为NULL。

3. RIGHT JOIN（右连接）

右连接会返回右表中所有的行，以及与左表匹配的行。如果左表中没有匹配的行，则结果中左表的列会被填充为NULL。下面是RIGHT JOIN的语法：

SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.列名 = 表2.列名;

还是以上面的示例为基础，我们使用RIGHT JOIN来查询学生的基本信息和对应的成绩。

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

查询结果如下：

id | name    | subject | score
---|---------|---------|------
1  | Alice   | Math    | 90.0
1  | Alice   | English | 85.0
2  | Bob     | Math    | 92.0
2  | Bob     | English | 88.0
3  | Charlie | Math    | 95.0

由于右表grades中的每一行都能与左表students中的某一行匹配上，所以结果中没有填充为NULL的列。

4. FULL OUTER JOIN（全外连接）

全外连接会返回左表和右表中所有的行，如果左表或者右表中没有匹配的行，则对应的列会被填充为NULL。在MySQL中，没有内置的FULL OUTER JOIN语法，但可以使用UNION ALL和LEFT JOIN、RIGHT JOIN的组合来实现。下面是使用UNION ALL和LEFT JOIN、RIGHT JOIN的组合来实现FULL OUTER JOIN的语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名
UNION ALL
SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.列名 = 表2.列名
WHERE 表1.列名 IS NULL;

下面是使用UNION ALL和LEFT JOIN、RIGHT JOIN的组合来实现FULL OUTER JOIN的示例：

SELECT students.id, students.name, grades.subject, grades.score
FROM students
LEFT JOIN grades
ON students.id = grades.student_id
UNION ALL
SELECT students.id, students.name, grades.subject, grades.score
FROM students
RIGHT JOIN grades
ON students.id = grades.student_id
WHERE students.id IS NULL;

查询结果如下：

id | name    | subject | score
---|---------|---------|------
1  | Alice   | Math    | 90.0
1  | Alice   | English | 85.0
2  | Bob     | Math    | 92.0
2  | Bob     | English | 88.0
3  | Charlie | Math    | 95.0
3  | Charlie | NULL    | NULL
NULL | NULL  | Science | 80.0

可以看到，FULL OUTER JOIN返回了左表students和右表grades中的所有行，并且填充了没有匹配的列。

5. CROSS JOIN（交叉连接）

交叉连接会返回两个表中的所有行的组合。它没有使用连接条件，所以结果是两个表的乘积。下面是CROSS JOIN的语法：

SELECT 列名
FROM 表1
CROSS JOIN 表2;

为了避免结果过大，我们以较小的表 students（3行）和较小的表grades（5行）为例进行演示。

SELECT students.id, students.name, grades.subject, grades.score
FROM students
CROSS JOIN grades;

查询结果如下：

id | name    | subject | score
---|---------|---------|------
1  | Alice   | Math    | 90.0
1  | Alice   | English | 85.0
1  | Alice   | Math    | 92.0
1  | Alice   | English | 88.0
1  | Alice   | Math    | 95.0
2  | Bob     | Math    | 90.0
2  | Bob     | English | 85.0
2  | Bob     | Math    | 92.0
2  | Bob     | English | 88.0
2  | Bob     | Math    | 95.0
3  | Charlie | Math    | 90.0
3  | Charlie | English | 85.0
3  | Charlie | Math    | 92.0
3  | Charlie | English | 88.0
3  | Charlie | Math    | 95.0

交叉连接返回了students表和grades表的所有行的组合。

6. UNION（合并查询结果）

UNION操作用于合并两个或多个SELECT语句的查询结果，并去除重复的行。合并的SELECT语句必须具有相同的列数和相似类型的数据。下面是UNION的语法：

SELECT 列名
FROM 表1
UNION
SELECT 列名
FROM 表2;

继续以上面的示例为基础，我们使用UNION来合并两个查询结果。

SELECT id, name
FROM students
WHERE id < 3
UNION
SELECT student_id, subject
FROM grades
WHERE student_id < 3;

查询结果如下：

id | name
---|---------
1  | Alice
2  | Bob
1  | Math
2  | English

UNION操作合并了students表中id小于3的学生信息和grades表中学生id小于3的课程信息，并去除了重复行。

结论

合并两张表是在MySQL中进行复杂查询和分析的常用操作。本文介绍了INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN和UNION等常用的合并两张表的方法和使用示例。

INNER JOIN基于连接条件返回两个表中的匹配行。
LEFT JOIN返回左表中的所有行和与右表匹配的行。
RIGHT JOIN返回右表中的所有行和与左表匹配的行。
FULL OUTER JOIN返回左表和右表中的所有行，并填充没有匹配的列。
CROSS JOIN返回两个表所有行的组合。
UNION合并两个查询结果，并去除重复行。

通过合理使用这些连接类型，可以实现灵活多样的数据查询和分析需求。

MySQL合并两张表