如何基于稀疏关联表实现三张表关联，查询公司产品订阅状态？

嘿，这个问题我碰到过好多次，你的稀疏表设计其实特别合理——毕竟没人想每次新增产品就去更新几百上千条公司记录对吧？下面给你几个实用的查询方案，轻松搞定已订阅和未订阅产品的查询需求：

这个是最基础的，直接通过关联表匹配即可：

SELECT p.*
FROM products p
JOIN companies_products cp ON p.product_id = cp.product_id
JOIN companies c ON cp.company_id = c.company_id
WHERE c.company_id = '你的目标公司ID'; -- 也可以用公司名过滤，比如 c.company_name = 'XX科技有限公司'

这里需要先生成「该公司+所有产品」的全量组合，再排除已经存在于关联表中的记录，用左连接配合IS NULL就能实现：

SELECT p.*
FROM products p
-- 先锁定目标公司，避免生成所有公司的全量组合
CROSS JOIN (SELECT company_id FROM companies WHERE company_id = '你的目标公司ID') target_company
LEFT JOIN companies_products cp 
  ON p.product_id = cp.product_id 
  AND target_company.company_id = cp.company_id
-- 左连接后找不到关联记录的，就是未订阅的产品
WHERE cp.product_id IS NULL;

如果需要统计所有公司对所有产品的订阅情况，同样可以用交叉连接+左连接，再加一个CASE字段标记状态：

SELECT 
    c.company_id,
    c.company_name,
    p.product_id,
    p.product_name,
    CASE WHEN cp.product_id IS NOT NULL THEN '已订阅' ELSE '未订阅' END AS subscribe_status
FROM companies c
CROSS JOIN products p
LEFT JOIN companies_products cp 
  ON c.company_id = cp.company_id 
  AND p.product_id = cp.product_id
ORDER BY c.company_id, p.product_id;

如果公司和产品的数据量比较大，交叉连接可能会生成较多临时数据，建议给companies_products表的(company_id, product_id)字段加一个联合主键或联合索引，这样能大幅提升左连接的匹配效率，避免查询变慢。

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