Months ago, Mr. Tom asked me some questions about SQL, and I answered him via E-mail. Here is a public version of answer.

Differences between several JOINs

顾名思义，JOIN 就是根据给定的键聚合两张表。

1.1 INNER JOIN

又作JOIN，对于我个人而言，这个是最常用的。 从集合的角度上来讲，当你使用 INNER JOIN 聚合两张表的时候，结果集为这两张表的交集。

1.2 LEFT JOIN vs RIGHT JOIN

SELECT
	`stu`.`id`,
    `stu`.`name`,
    `t`.`id`,
    `t`.`name`
FROM `Student` `stu`
LEFT JOIN `Teacher` `t`
	ON `stu`.`teacher_id` = `t`.`id`;

以上面的 SQL 为例，我们称 ON 左侧的为 LEFT，右侧的为 RIGHT，那么结果集的包含关系如下所示。

LEFT JOIN => (LEFT ∩ RIGTH) ∪ LEFT
RIGHT JOIN => (LEFT ∩ RIGTH) ∪ RIGHT

1.3 OUTER JOIN or FULL OUTER JOIN

MySQL 实际上是没有 OUTER JOIN 这个语句的（PostgreSQL 有这个语句）。承接 1.2 所示的 LEFT 和 RIGHT 的概念，OUTER JOIN 的结果集如下所示：

OUTER JOIN => LEFT ∪ RIGHT

PrimaryKey (PK) vs ForeignKey (FK)

首先，需要强调的是，虽然「主键」和「外键」都有一个「键」，但这两个是完全不同的两个概念。不要被字面含义所迷惑。

2.1 PrimaryKey

一般而言，每张表会有一列或者多列的集合作为该表中每一行的唯一标识，这个标识被称为「主键」。每张表只能有一个主键，主键的值在该表中唯一。

当你指定某一列或某几列为「主键」时，存储引擎会自动给这一列或几列添加一个 unique 索引。

2.2 ForeignKey

「外键」一般而言是指定一列或者多列的集合用于与另一张表建立连接，其值是另一张表中对应行主键的值。

举一个栗子

这里有两张表：Teacher 和 Student，其中 Student 包含了一个外键指向 Teacher。

CREATE TABLE `Teacher` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL
);

CREATE TABLE `Student` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `teacher_id` INTEGER DEFAULT NULL,
    
    FOREIGN KEY fk__teacher__teacher_id (`teacher_id`) REFERENCES `Teacher`(`id`)
);

如下图所示，可以很明显地看出两张表之间的关系。

向表中插入一些数据

INSERT INTO `Teacher`(`id`, `name`) VALUES (1, 'teacher1');
INSERT INTO `Student`(`name`, `teacher_id`) VALUES ('student1', 1);
INSERT INTO `Student`(`name`, `teacher_id`) VALUES ('student2', 2);

执行上述 SQL，结果是最后一条插入语句报错

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`hammer`.`Student`, CONSTRAINT `fk__teacher__teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `Teacher` (`id`))

这个错误正是由于最后一条插入语句中的 teacher_id 是一个无效的值，在「外键」的约束下，数据库提示插入失败。

对于一个已存在的表如果想要增加新的约束条件，那么表中已有数据是否需要重构？

这个取决于你表中的数据。如果数据符合新增加的约束，则不必重构；如果不符合，在把数据修改至符合约束的要求之前是无法成功添加新的约束的。

如果新的约束条件添加成功了，一般而言，是不会有预计效果之外的副作用的（前提是你的约束写的没毛病）。

References

• Understanding JOINs in MySQL and Other Relational Databases
• Primary and Foreign Key Constraints

备注：

1. 本文档中撰写的 SQL 均适用于 MySQL。如果你使用其他的数据库比如 PostgreSQL 或者 MSSQL，你需要根据对应数据库的语法对 SQL 做相应的修改。

2. MySQL 的 OUTER JOIN 其实是先分别 LEFT JOIN 和 RIGHT JOIN，然后用 UNION 将结果集合并。

3. 完整示例

   CREATE TABLE `Teacher` (
   `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(32) NOT NULL
   );
   
   CREATE TABLE `Student` (
   `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(32) NOT NULL,
   `teacher_id` INTEGER DEFAULT NULL,
   
   FOREIGN KEY fk__teacher__teacher_id (`teacher_id`) REFERENCES `Teacher`(`id`)
   );
   
   TRUNCATE TABLE `Teacher`;
   
   INSERT INTO `Teacher`(`id`, `name`) VALUES (1, 'teacher1');
   INSERT INTO `Student`(`name`, `teacher_id`) VALUES ('student1', 1);
   
   -- Returns error.
   INSERT INTO `Student`(`name`, `teacher_id`) VALUES ('student2', 2);
   
   INSERT INTO `Student`(`name`, `teacher_id`) VALUES ('student2', NULL);
   INSERT INTO `Teacher` (`id`, `name`) VALUES (2, 'teacher2');
   
   
   SELECT
       `stu`.`id`,
       `stu`.`name`,
       `t`.`id`,
       `t`.`name`
   FROM `Student` `stu`
   JOIN `Teacher` `t`
       ON `stu`.`teacher_id` = `t`.`id`;