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 所示的 LEFTRIGHT 的概念,OUTER JOIN 的结果集如下所示:

OUTER JOIN => LEFT ∪ RIGHT

PrimaryKey (PK) vs ForeignKey (FK)

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

2.1 PrimaryKey

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

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

2.2 ForeignKey

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

举一个栗子

这里有两张表:TeacherStudent,其中 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`)
);

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

向表中插入一些数据

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


备注:

  1. 本文档中撰写的 SQL 均适用于 MySQL。如果你使用其他的数据库比如 PostgreSQL 或者 MSSQL,你需要根据对应数据库的语法对 SQL 做相应的修改。
  2. 完整示例
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`;
  1. MySQL 的 OUTER JOIN 其实是先分别 LEFT JOINRIGHT JOIN,然后用 UNION 将结果集合并。