如何区分复合键查询中项目不存在与部件不存在的情况？

问题描述 #

应用中projects表与widgets表为一对多关系，每个项目包含多个部件，采用**复合键（slug, code）**标识部件，例如(p1, w2)和(p2, w2)为不同部件，为项目所有者提供独立作用域。

需要一条SQL语句，通过复合键查询部件时，能明确区分以下三种情况：

• projects.slug不存在
• 项目存在，但对应widgets.code不存在
• 项目slug与部件code完全匹配

尝试过以下查询，但无法区分项目不存在和部件不存在的场景：

test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'p3' AND code = 'w2';
id | slug | id | code 
----+------+----+------
(0 rows)

test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'missing-project' AND code = 'w2';
 id | slug | id | code 
----+------+----+------
(0 rows)

第一个查询是项目p3存在但无w2部件，第二个是项目slug不存在，两者均返回空结果，无法区分原因。

已知背景 #

已掌握查询项目所有部件时区分项目不存在与项目无部件的方法，但单个复合键查询场景仍需解决方案，示例如下：

test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id 
WHERE p.slug = 'p1';
 id | slug | id | code 
----+------+----------+----------
  1 | p1   | 51 | w1
  1 | p1   | 52 | w2
(2 rows)


test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id 
WHERE p.slug = 'p3';
 id | slug | id | code 
----+------+----+------
  3 | p3   |  0 | 
(1 row)


test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id 
WHERE p.slug = 'missing-project';
 id | slug | id | code 
----+------+----+------
(0 rows)

数据库表结构与初始化数据 #

CREATE TABLE projects (
  id    INTEGER,
  slug  VARCHAR(32),
  PRIMARY KEY (id)
);

CREATE TABLE widgets (
  id          INTEGER NOT NULL,
  project_id  INTEGER NOT NULL,
  code        VARCHAR(32) NOT NULL,
  PRIMARY KEY (id, project_id),

  CONSTRAINT widgets_project_id_fkey FOREIGN KEY (project_id)
     REFERENCES projects (id)
);

INSERT INTO projects (id, slug) VALUES (1, 'p1');
INSERT INTO projects (id, slug) VALUES (2, 'p2');
INSERT INTO projects (id, slug) values (3, 'p3');

INSERT INTO widgets (id, project_id, code) VALUES (51, 1, 'w1');
INSERT INTO widgets (id, project_id, code) VALUES (52, 1, 'w2');
INSERT INTO widgets (id, project_id, code) VALUES (53, 2, 'w2');

解决方案 #

核心思路是把部件的code条件移到JOIN的ON子句中（而非WHERE子句），同时新增状态字段明确标识当前结果所属情况。

最终SQL语句 #

SELECT
  p.id AS project_id,
  p.slug AS project_slug,
  COALESCE(w.id, 0) AS widget_id,
  COALESCE(w.code, '') AS widget_code,
  CASE
    WHEN p.id IS NULL THEN '项目不存在'
    WHEN w.id IS NULL THEN '项目存在但部件不存在'
    ELSE '匹配成功'
  END AS status
FROM (SELECT * FROM projects WHERE slug = '要查询的slug') p
LEFT JOIN widgets w ON p.id = w.project_id AND w.code = '要查询的code';

逻辑说明 #

1. 子查询锁定目标项目：先用子查询获取指定slug的项目，确保即使项目不存在，也能通过LEFT JOIN的特性保留判断入口。
2. LEFT JOIN绑定部件条件：将w.code = 'xxx'放到JOIN的ON子句中，这样当项目存在但无对应code的部件时，会返回项目信息，而widget字段为NULL。
3. CASE语句区分状态：通过判断p.id和w.id是否为NULL，明确标识三种状态：
   • p.id IS NULL：项目slug不存在
   • w.id IS NULL：项目存在，但对应code的部件不存在
   • 其他情况：项目和部件完全匹配成功

测试示例 #

情况1：项目不存在（slug='missing-project'，code='w2'）

SELECT
  p.id AS project_id,
  p.slug AS project_slug,
  COALESCE(w.id, 0) AS widget_id,
  COALESCE(w.code, '') AS widget_code,
  CASE
    WHEN p.id IS NULL THEN '项目不存在'
    WHEN w.id IS NULL THEN '项目存在但部件不存在'
    ELSE '匹配成功'
  END AS status
FROM (SELECT * FROM projects WHERE slug = 'missing-project') p
LEFT JOIN widgets w ON p.id = w.project_id AND w.code = 'w2';

结果：

project_id | project_slug | widget_id | widget_code |    status    
------------+--------------+-----------+-------------+--------------
            |              |         0 |             | 项目不存在
(1 row)

情况2：项目存在但部件不存在（slug='p3'，code='w2'）

SELECT
  p.id AS project_id,
  p.slug AS project_slug,
  COALESCE(w.id, 0) AS widget_id,
  COALESCE(w.code, '') AS widget_code,
  CASE
    WHEN p.id IS NULL THEN '项目不存在'
    WHEN w.id IS NULL THEN '项目存在但部件不存在'
    ELSE '匹配成功'
  END AS status
FROM (SELECT * FROM projects WHERE slug = 'p3') p
LEFT JOIN widgets w ON p.id = w.project_id AND w.code = 'w2';

结果：

project_id | project_slug | widget_id | widget_code |          status          
------------+--------------+-----------+-------------+--------------------------
          3 | p3           |         0 |             | 项目存在但部件不存在
(1 row)

情况3：匹配成功（slug='p1'，code='w2'）

SELECT
  p.id AS project_id,
  p.slug AS project_slug,
  COALESCE(w.id, 0) AS widget_id,
  COALESCE(w.code, '') AS widget_code,
  CASE
    WHEN p.id IS NULL THEN '项目不存在'
    WHEN w.id IS NULL THEN '项目存在但部件不存在'
    ELSE '匹配成功'
  END AS status
FROM (SELECT * FROM projects WHERE slug = 'p1') p
LEFT JOIN widgets w ON p.id = w.project_id AND w.code = 'w2';

结果：

project_id | project_slug | widget_id | widget_code |  status  
------------+--------------+-----------+-------------+----------
          1 | p1           |        52 | w2          | 匹配成功
(1 row)

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