数据库基本知识

JOIN，LEFT JOIN，RIGHT JOIN，FULL JOIN

概念

JOIN

在SQL中，JOIN 是用来结合两个或多个表中的行的操作，ON 子句用于指定 JOIN 的条件。

JOIN可以独立使用，并不一定要配合ON，ON只是过滤条件。

JOIN其实是INNER JOIN的简写（SQL语句），表示只返回两个表中都有匹配的行，如果有某个表不匹配，则该行不会被查询出来。

在使用 INNER JOIN 时，哪个表是主表并不重要，因为 INNER JOIN 只会返回两个表中都有匹配的行。

LEFT JOIN（LEFT OUTER JOIN）

LEFT JOIN在处理不匹配的行时与JOIN有不同的行为。

LEFT JOIN 返回左表（FROM 子句中的第一个表）的所有行，即使在右表中没有匹配的行。

如果左表中的行在右表中没有匹配，结果集中的那些行将与右表中的列一起显示为 NULL。

RIGHT JOIN（RIGHT OUTER JOIN）

与LEFT JOIN类似，返回右表（JOIN 的表）的所有行，即使在右表中没有匹配的行。

FULL JOIN（FULL OUTER JOIN）

与LEFT JOIN类似，返回左右表（JOIN 的表）的所有行，即使在左右表中没有匹配的行。

示例

准备数据

定义主表（用户表）

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

INSERT INTO users (username) VALUES ('Alice'), ('Bob');

定义数组元素表（颜色表）

CREATE TABLE colors (
    color_id INT PRIMARY KEY AUTO_INCREMENT,
    color_name VARCHAR(50) NOT NULL
);

INSERT INTO colors (color_name) VALUES ('Red'), ('Green'), ('Blue');

定义关联表（用户颜色偏好表）

CREATE TABLE user_color_preferences (
    user_id INT,
    color_id INT,
    PRIMARY KEY (user_id, color_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (color_id) REFERENCES colors(color_id)
);

INSERT INTO user_color_preferences (user_id, color_id) VALUES (1, 1), (1, 2);
INSERT INTO user_color_preferences (user_id, color_id) VALUES (2, 2), (2, 3);

测试JOIN

SELECT *
FROM users u
JOIN user_color_preferences

可以看到，JOIN可以单独使用，其中users中有2条数据，user_color_preferences中有4条数据，我们查看会得到2x4=8条数据，相当于主表每一条数据都会一一对应JOIN的表的每条数据。

测试JOIN ON

SELECT *
FROM users u
JOIN user_color_preferences ucp ON u.user_id = ucp.user_id

可以看到，使用ON进行条件限制，该条件相当于在前面小节中增加u.user_id = ucp.user_id的限制。

测试多个JOIN

SELECT *
FROM users u
JOIN user_color_preferences ucp
JOIN colors c;

可以看到，JOIN可以单独使用，其中users中有2条数据，user_color_preferences中有4条数据，colors中有3条数据，我们查看会得到2x4x3=24条数据，相当于主表每一条数据都会一一对应JOIN的表的每条数据，JOIN的表的每条数据都会一一对应下一个JOIN的表的每条数据。

测试多个JOIN ON

SELECT *
FROM users u
JOIN user_color_preferences ucp ON u.user_id = ucp.user_id
JOIN colors c ON ucp.color_id = c.color_id;

可以看到，使用ON进行条件限制，该条件相当于在前面小节中增加u.user_id = ucp.user_id和ucp.color_id = c.color_id的限制。

另外可以将SELECT *修改为SELECT u.username,c.color_name，来过滤出用户喜欢的颜色：

测试LEFT JOIN

我们删除user_color_preferences的user_id为2的数据，只保留user_id为1的两条数据。

SELECT *
FROM users u
LEFT JOIN user_color_preferences ucp ON u.user_id = ucp.user_id

可以看到，尽管user_id为2的在user_color_preferences表中无数据，但是我们还是查询出来，只是相关的字段为NULL。

总结

对于多个JOIN，通过观察示例，其实是主表和第一个JOIN的表关联后的结果，再与第二JOIN的表进行关联。

为了方便理解，我们可以忽略select和where的语句，观察主表和各个JOIN的表的结果后，再进行where及select。