为什么此查询仅返回非空子表的结果？(Why does this query only return results with non-empty child tables?)

 这是我们运行的查询的简化版本，我们需要在父表中查找子行匹配的所有行。 当其中一个子表为空时，下面的查询不返回任何结果。 
 
 主表有两个子表： 
 
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主表是资产管理系统中的会话表，用于记录客户端查找的资产。 有不同类型的查找，每种类型都有一个单独的子表，另外还有一个属性子表，用于可以搜索的会话的键/值对。 

This is a simplified version of a query we are running where we need to find all rows in the main parent table where the child rows match. The query below returns no results when one of the child tables is empty.
 
The main table has two child tables:
 
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');
 
There are no rows in child2 and the following query returns no rows when it is empty:
 
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';
 
If a row is added to child2, even if it doesn't match the WHERE clause, then the SELECT does return the row in the main table.
 
INSERT INTO child2 (id, main_id, name) VALUES (4, 1, 'child2');
 
I've tested this on Derby and SQLite, so this looks to be something general with databases.
 
Why is this behaving this way?
 
What can I do to fix it?
 
I could change to UNION separate SELECTs, but that's much more verbose, and plus, we're generating the SQL dynamically and I'd rather not have to change our code.
 
Another fix is just to add a dumb row to the database, but that's messy.
 
PS The main table is a session table in an asset management system that records the assets that clients look up. There are different types of lookups and each kind gets a separate child table, plus there is an attributes child table for key/value pairs for the session that can be searched on.

原文：https://stackoverflow.com/questions/842902