关系型数据库把不同类型的数据放在不同表中。任务只保存 project_id,项目只保存 owner_id,不会在每条任务中重复项目名称和用户邮箱。
project_id
owner_id
当接口需要同时返回任务、项目和负责人信息时,就要根据这些字段之间的关系重新组合表中的行。SQL 使用 JOIN 表达这种关联。
JOIN
JOIN 本身并不要求数据库已经定义外键,它只按照连接条件判断两行是否匹配。外键的职责是保证 tasks.project_id、projects.owner_id 等引用值确实存在,从而让这些连接关系长期保持可靠。
tasks.project_id
projects.owner_id
现有关系如下:
tasks.project_id 引用 projects.id,projects.owner_id 引用 users.id。任务、项目和用户分开保存,查询通过关联字段组合结果。箭头指向被引用的表,每个项目可有多条任务,每个用户可拥有多个项目。
TABLE RELATIONS
tasks
projects
id · owner_id
users
id
常见连接类型如下:
INNER JOIN
LEFT JOIN
NULL
RIGHT JOIN
FULL JOIN
CROSS JOIN
实际业务最常使用 INNER JOIN 和 LEFT JOIN,本文也会重点讲解这两种连接,最后再说明 CROSS JOIN 的用途和风险。
读取任务及其项目名称:
1SELECT 2 tasks.id, 3 tasks.title, 4 projects.name AS project_name 5FROM tasks 6JOIN projects ON projects.id = tasks.project_id;
ON 后面的条件说明两张表的行如何匹配。对于每条任务,数据库找到 projects.id 与 tasks.project_id 相等的项目,再把需要的列放进同一结果行。
ON
projects.id
这里的 JOIN 默认等同于 INNER JOIN,只返回两边都能匹配的行。由于任务外键要求项目存在,正常数据都能找到对应项目。连接只会产生本次查询使用的结果集,不会把三张表合并存储,也不会修改原表。
多表查询中经常出现同名列,因此应该给表设置简短且能辨认的别名:
01SELECT 02 t.id AS task_id, 03 t.title AS task_title, 04 t.status, 05 p.id AS project_id, 06 p.name AS project_name, 07 u.id AS owner_id, 08 u.display_name AS owner_name 09FROM tasks AS t 10JOIN projects AS p ON p.id = t.project_id 11JOIN users AS u ON u.id = p.owner_id 12WHERE t.archived_at IS NULL 13ORDER BY t.created_at DESC, t.id DESC;
别名只在当前 SQL 中有效,不会修改真实表名。为表指定别名后,同一层查询应该通过别名引用它,例如写了 FROM tasks AS t 后就继续使用 t.id,而不是 tasks.id。t、p、u 适合短查询;查询很复杂时,也可以使用 task、project、owner 等更有意义的别名。
FROM tasks AS t
t.id
tasks.id
t
p
u
task
project
owner
限定列名前缀不仅用于解决歧义,也能说明数据来源。看到 u.display_name,读者不必回到表结构中猜测名称来自哪张表。
u.display_name
此段落已有评论,按 Enter 查看评论及回复列表。
连接 GitHub 后查看和发表评论,也可在 GitHub 查看公开讨论。
1. 关联查询
关系型数据库把不同类型的数据放在不同表中。任务只保存
project_id,项目只保存owner_id,不会在每条任务中重复项目名称和用户邮箱。当接口需要同时返回任务、项目和负责人信息时,就要根据这些字段之间的关系重新组合表中的行。SQL 使用
JOIN表达这种关联。JOIN本身并不要求数据库已经定义外键,它只按照连接条件判断两行是否匹配。外键的职责是保证tasks.project_id、projects.owner_id等引用值确实存在,从而让这些连接关系长期保持可靠。现有关系如下:
tasks.project_id 引用 projects.id,projects.owner_id 引用 users.id。任务、项目和用户分开保存,查询通过关联字段组合结果。箭头指向被引用的表,每个项目可有多条任务,每个用户可拥有多个项目。
TABLE RELATIONS
tasks
project_id
projects
id · owner_id
users
id
常见连接类型如下:
INNER JOINLEFT JOINNULL补齐RIGHT JOINNULL补齐FULL JOINNULL补齐CROSS JOIN实际业务最常使用
INNER JOIN和LEFT JOIN,本文也会重点讲解这两种连接,最后再说明CROSS JOIN的用途和风险。读取任务及其项目名称:
ON后面的条件说明两张表的行如何匹配。对于每条任务,数据库找到projects.id与tasks.project_id相等的项目,再把需要的列放进同一结果行。这里的
JOIN默认等同于INNER JOIN,只返回两边都能匹配的行。由于任务外键要求项目存在,正常数据都能找到对应项目。连接只会产生本次查询使用的结果集,不会把三张表合并存储,也不会修改原表。2. 表与列别名
多表查询中经常出现同名列,因此应该给表设置简短且能辨认的别名:
别名只在当前 SQL 中有效,不会修改真实表名。为表指定别名后,同一层查询应该通过别名引用它,例如写了
FROM tasks AS t后就继续使用t.id,而不是tasks.id。t、p、u适合短查询;查询很复杂时,也可以使用task、project、owner等更有意义的别名。限定列名前缀不仅用于解决歧义,也能说明数据来源。看到
u.display_name,读者不必回到表结构中猜测名称来自哪张表。