为什么这个查询只返回非空子表的结果？

Question

这是我们正在运行的查询的简化版本，我们需要在主父表中查找子行匹配的所有行。 当子表之一为空时，下面的查询不会返回结果。

主表有两个子表：

CREATE TABLE main (id INT PRIMARY KEY, name VARCHAR(8));

CREATE TABLE child1(id INT PRIMARY KEY, main_id int, name VARCHAR(8));
ALTER TABLE child1 add constraint fk_child1_main foreign key (main_id) references main (id);

CREATE TABLE child2(id INT PRIMARY KEY, main_id int, name VARCHAR(8));
ALTER TABLE child2 add constraint fk_child2_main foreign key (main_id) references main (id);

INSERT INTO main (id, name) VALUES (1, 'main');
INSERT INTO child1 (id, main_id, name) VALUES (2, 1, 'child1');

child2 中没有行，以下查询在为空时不返回任何行：

SELECT
  main.*
FROM
  main
INNER JOIN
  child1
ON
  main.id = child1.main_id
INNER JOIN
  child2
ON
  main.id = child2.main_id
WHERE
  child1.name = 'child1' OR
  child2.name = 'DOES NOT EXIST';

如果向 child2 添加一行，即使它与 WHERE 子句不匹配，SELECT 也会返回主表中的行。

INSERT INTO child2 (id, main_id, name) VALUES (4, 1, 'child2');

我已经在 Derby 和 SQLite 上对此进行了测试，因此这看起来对于数据库来说是通用的。

为什么会这样？

我能做什么来修复它？

我可以更改为 UNION 单独的 SELECT，但这更加冗长，而且，我们动态生成 SQL，我宁愿不必更改我们的代码。

另一个修复方法是向数据库添加一个哑行，但这很混乱。

PS 主表是资产管理系统中的一个会话表，记录了客户端查找的资产。 有不同类型的查找，每种类型都有一个单独的子表，此外还有一个用于可搜索会话的键/值对的属性子表。

Answer 1

当 child2 没有行时，由于与 child2 表的内部联接，查询不会返回任何行。 如果您内联到没有行的表，您将永远不会得到任何结果 - 如果您想在 child2 为空时获得结果，则必须外联到 child2。

当child2确实有一行时，查询返回结果的原因是where子句：

WHERE
  child1.name = 'child1' OR
  child2.name = 'DOES NOT EXIST';

内连接表示child2中必须有具有匹配ID的内容，但是where子句中有一个OR，所以你会得到结果只是因为 child1.name = 'child1'。 之后，数据库就不必费心查看 child2 表。

解决这个问题：

我预感您只想在满足某些条件时返回子行。 您应该对它们两者进行外连接，也许还可以将额外的条件从 where 子句移至 join 子句，如下所示：

SELECT
  main.*
FROM
  main
LEFT OUTER JOIN
  child1
ON
  main.id = child1.main_id
  AND child1.name = 'child1'
LEFT OUTER JOIN
  child2
ON
  main.id = child2.main_id
  AND child2.name = 'whatever'

• 外连接意味着即使一个表为空，您也有机会获得结果。

• 将额外条件 (child1.name = ...) 从 WHERE 子句移至外连接意味着您仅在条件为 true 时才能获取表信息。 （我认为这可能是您想要做的，但也许不是，在这种情况下，将条件保留在您最初拥有它们的 WHERE 子句中。）

Answer 2

它不返回任何内容，因为您正在使用内部联接。

将内连接更改为左连接

Answer 3

当您说 INNER JOIN 时，您要求查询返回在连接两侧都有结果的行。 这意味着任何没有匹配子行的行都将被删除。

听起来您正在寻找的是 LEFT JOIN ，它将包括连接（主）左侧的所有行，即使它们在右侧没有匹配的条目（child1，child2）。

这是标准行为，对于不熟悉 SQL 的人来说也是一个非常常见的问题。 Wikipedia 包含所有详细信息，否则快速 Google 搜索 会带来大量结果。