真实的数据库从不把所有数据放在一张表里。客户在一张表,订单在另一张表。要回答“本月哪些客户下了订单?”,就必须把它们合并起来,这正是 SQL JOIN 的作用。

这篇 SQL JOIN 指南讲究实用。同样的两张表,不同的连接类型会得到不同的结果,所以 JOIN 常常让初学者困惑。只要用小而具体的数据来看,它们就变得简单了。

SQL JOIN 详解:示例表

customers

orders

Chitra 没有订单,而订单 104 指向一个不存在的客户(9)。这两种边界情况能清楚地展示各种连接类型的区别。

INNER JOIN

INNER JOIN 只返回条件在两张表中都匹配的行,没有对应行的数据会被丢弃。

SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

结果:Asha (101)、Asha (102)、Ben (103)。Chitra 和订单 104 都不在其中。当你只关心有关联的数据时使用它,例如订单及其客户信息。单独写 JOIN 就等于 INNER JOIN。

LEFT JOIN

LEFT JOIN 保留左表的所有行,并附加右表中匹配的行。没有匹配时,右侧列为 NULL。

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

结果:Asha (101)、Asha (102)、Ben (103)、Chitra (NULL, NULL)。现在 Chitra 出现了。

RIGHT JOIN 与 FULL JOIN

RIGHT JOIN 与 LEFT JOIN 相反,保留右表的所有行。大多数人会调换表的顺序并使用 LEFT JOIN,因为那样读起来更自然。

SELECT c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

结果包含订单 104,其客户名为 NULL。FULL (OUTER) JOIN 返回两张表的所有行:没有订单的 Chitra 和没有客户的订单 104。

SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

PostgreSQL 和 SQL Server 支持它;MySQL 不支持,需要用 UNION 合并 LEFT JOIN 与 RIGHT JOIN。想了解更多,PostgreSQL 手册中有一份清晰的连接教程。

CROSS JOIN 与 SELF JOIN

CROSS JOIN 把一张表的每一行与另一张表的每一行配对:三个客户和四个订单会产生十二行。它很少用,但适合生成组合,例如为每种尺寸和颜色生成测试数据。

SELECT c.name, o.id
FROM customers c
CROSS JOIN orders o;

SELF JOIN 使用别名把一张表与它自己连接,例如查找每位员工的经理。

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SQL JOIN:常见错误与性能技巧

• 漏写 ON 子句会意外变成 CROSS JOIN,使行数暴增。
• 在 LEFT JOIN 之后于 WHERE 中过滤右表会把它变成 INNER JOIN;请把条件放在 ON 子句中。
• 列名有歧义:给列加上表别名前缀。
• 按非唯一列连接会使行数成倍增加,让合计虚高。
• 为连接中用到的列建立索引,只选择需要的列,并用 EXPLAIN 检查慢查询。

为什么 JOIN 对测试人员很重要

QA 工程师用 JOIN 验证通过界面或 API 创建的数据是否正确写入数据库,并查找孤立记录。可以结合我们的 Postman API 测试指南(英文)。基础知识请看 SQL 入门教程(英文),了解较新的数据存储可阅读向量数据库(英文)。要在本地运行数据库,请参见我们的 Docker 教程(英文)。

总结

一句话概括 SQL JOIN:INNER 只保留匹配项,LEFT 保留左边全部,RIGHT 保留右边全部,FULL 全部保留。先用两张小表练习,直到能在运行查询前预测结果;然后尝试连接三张表,并把 JOIN 与 GROUP BY 结合起来。

常见问题

继续学习(英文文章)