SQL-连接详解

示例数据

假设我们有两个表 students 和 scores，表结构如下：

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

CREATE TABLE scores (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

插入一些示例数据：

INSERT INTO students (id, name, age) VALUES
(1, 'Alice', 20),
(2, 'Bob', 22),
(3, 'Charlie', 21),
(4, 'David', 23);

INSERT INTO scores (student_id, subject, score) VALUES
(1, 'Math', 90),
(1, 'Science', 85),
(2, 'Math', 88),
(3, 'Science', 92),
(5, 'History', 80); -- 注意：student_id 5 在 students 表中不存在

1. 内连接（INNER JOIN）

内连接返回两个表中满足连接条件的记录。

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

查询结果：

name    | subject | score
--------|---------|-------
Alice   | Math    | 90
Alice   | Science | 85
Bob     | Math    | 88
Charlie | Science | 92

2. 左连接（LEFT JOIN）

左连接返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果集中对应字段的值为NULL。

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

查询结果：

name    | subject | score
--------|---------|-------
Alice   | Math    | 90
Alice   | Science | 85
Bob     | Math    | 88
Charlie | Science | 92
David   | NULL    | NULL

3. 右连接（RIGHT JOIN）

右连接返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则结果集中对应字段的值为NULL。

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

查询结果：

name    | subject | score
--------|---------|-------
Alice   | Math    | 90
Alice   | Science | 85
Bob     | Math    | 88
Charlie | Science | 92
NULL    | History | 80

4. 全外连接（FULL OUTER JOIN）

全外连接返回两个表中的所有记录，以及满足连接条件的记录。如果某个表中没有匹配的记录，则结果集中对应字段的值为NULL。

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

查询结果：

name    | subject | score
--------|---------|-------
Alice   | Math    | 90
Alice   | Science | 85
Bob     | Math    | 88
Charlie | Science | 92
David   | NULL    | NULL
NULL    | History | 80

总结

通过以上示例，我们可以清楚地看到不同连接类型的结果差异：

• 内连接（INNER JOIN）：只返回两个表中满足连接条件的记录。
• 左连接（LEFT JOIN）：返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果集中对应字段的值为NULL。
• 右连接（RIGHT JOIN）：返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则结果集中对应字段的值为NULL。
• 全外连接（FULL OUTER JOIN）：返回两个表中的所有记录，以及满足连接条件的记录。如果某个表中没有匹配的记录，则结果集中对应字段的值为NULL。