MySQL多表关联查询：如何筛选join表中人员关联自身所属公司的错误条目

我有一个基于4张MySQL表的应用，表结构如下：

persons
------------
id              -- 主键，唯一
name
entity_id       -- 外键关联 entities 表

entities
------------
id              -- 主键，唯一
name
company_id      -- 外键关联 companies 表

companies
------------
id              -- 主键，唯一
name

persons_linked_companies -- 人员与公司关联表
--------------------------
id              -- 主键，唯一
persons_id      -- 外键关联 persons 表
companies_id    -- 外键关联 companies 表

业务规则如下：

• 每个人员归属一个实体
• 每个实体归属一个公司
• 一个人员仅能关联一个实体
• 一个实体仅能关联一个公司
• 一个人员可以关联一个或多个其他第三方公司，关联关系存储在 persons_linked_companies 表中
• 人员可以关联多个公司，但不允许关联自己所属的公司

需求是查询 persons_linked_companies 表中的无效记录：即关联的公司和人员自身所属公司相同的条目。

你之前编写的伪代码存在语法问题，MySQL 不支持直接通过 表.关联表.字段 的链式写法访问跨表字段，需要通过关联查询或者子查询打通表关系后做对比。

方案1：关联查询（推荐） #

性能更优，可直接带出人员、关联公司的明细信息方便核对：

SELECT 
  plc.id AS invalid_link_id,
  plc.persons_id AS person_id,
  p.name AS person_name,
  plc.companies_id AS linked_company_id,
  c.name AS linked_company_name
FROM persons_linked_companies plc
INNER JOIN persons p ON plc.persons_id = p.id
INNER JOIN entities e ON p.entity_id = e.id
INNER JOIN companies c ON plc.companies_id = c.id
WHERE plc.companies_id = e.company_id;

方案2：子查询实现 #

如果仅需要获取无效关联记录的ID，可以用子查询写法：

SELECT id AS invalid_link_id
FROM persons_linked_companies plc
WHERE plc.companies_id = (
  SELECT e.company_id
  FROM persons p
  INNER JOIN entities e ON p.entity_id = e.id
  WHERE p.id = plc.persons_id
);

内容的提问来源于stack exchange，提问作者donmelchior