sql - 如何区分空结果集与不存在的外键关系？

Question

在我的应用程序中，projects 与widgets 具有一对多关系。每个项目都包含许多小部件。

在我的应用程序中，我使用复合键(code，slug)键入一个小部件，即 (p1, w2) 和 (p2, w2) 是不同的。项目 p1 有自己的小部件 w2，项目 p2 有一个不同的小部件 w2，代码相同。这为项目所有者提供了单独的范围。

我想要一个查询，它将通过复合键获取一个小部件，同时区分 projects.slug 不匹配与 widgets.code 不匹配匹配。

这是我到目前为止尝试过的方法。 WHERE 子句筛选特定项目 slug 和小部件代码，但无法确定项目 slug 是否丢失或小部件 code 是否丢失。

下面的第一个查询返回空结果，因为项目 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 = '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)

什么查询会产生可以区分不存在的项目 slug、具有不存在的小部件代码的现有项目以及项目 slug 和小部件代码的完全匹配的结果？

---

这个问题的背景:

我已经了解了如何使用查询来获取项目的所有小部件来执行此操作。 ( How to differentiate between no rows and foreign key reference not existing? )

如果我使用仅包含 slug 的查询，我检测到项目何时丢失(空结果集 - 下面的第二条语句)与项目存在但没有小部件(1 行，id 列为空 - 下面的第三条语句)。

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)

SELECT
  p.id, p.slug, COALESCE(w.id, 0), COALESCE(w.code, '')
FROM projects p
LEFT OUTER JOIN widgets w
  ON p.id = w.project_id
WHERE p.slug = 'p4';

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');

Answer 1

如果我理解得很好，您需要区分三种情况:

1. 项目和小部件存在。
2. 项目存在，但小部件不存在。
3. 该项目不存在。

如果这是对案例的正确描述，您可以将小部件搜索放在连接的 ON 子句中，并将项目搜索放在 WHERE 子句中查询。

例如:

SELECT
  p.id, 
  p.slug, 
  COALESCE(w.id, 0) as id, 
  COALESCE(w.code, '') as code
FROM projects p 
LEFT OUTER JOIN widgets w ON p.id = w.project_id 
                         AND w.code = 'w1' -- widget code
where p.slug = 'p3' -- project code

案例 #1 结果:

id  slug  id  code
--  ----  --  ----
 1  p3    10  w1

案例 #2 结果:

id  slug  id  code
--  ----  --  ----
 1  p3    10  <null>

案例 #3 结果:

id  slug  id  code
--  ----  --  ----
-- 0 rows returned

虽然它仍然返回一行，但可以通过显示为空的 code 列来区分 case #2 和 case #1。案例 #3 根本不返回任何行。

请参阅 db<>fiddle 中的运行示例.

您原来的方法的问题是 WHERE 条件 w.code = 'w2' 有效地将您的左外连接变成了内连接:任何结果widget 端的 NULL 将被该条件消除。这就是为什么你从来没有得到这些结果。如果您改为将该条件放在连接条件中，则不会消除此类结果。这样做的原因是左外部连接的定义方式:它们是内部连接加上结果中缺少的左侧行的结果，并在右侧补充了 NULL。您的条件会从内部联接中删除该行，因此它出现在 widget 端带有 NULL 的结果中。