MySQL连接表而不重复每个表值

Question

嘿，我正在尝试连接 2 个表，但左侧映射到所有条目。我正在避免这种情况，因为我希望合并表，但在应有的地方使用 NULL 值

测试表00:

ownership membership
Johnny    Active

测试表01:

pet petname
dog John
cat timmy
parrot Johns

当我这样加入他们时:

SELECT * FROM (select * from testtable00 inner join testtable01) as u where u.ownership like "%John%";

ownership membership pet petname
Johnny   Active      dog John
Johnny   Active      parrot Johns

我想要实现的是

ownership membership pet petname
Johnny   Active      NULL NULL
NULL       NULL      dog John
NULL       NULL      parrot Johns

Answer 1

因为表之间没有关系，所以您不应该执行 JOIN。您想要的结果看起来像一个 UNION:

SELECT ownership, membership, NULL AS pet, NULL as petname
FROM testtable00
WHERE ownership like '%John%'
UNION ALL
SELECT NULL AS ownership, NULL AS membership, pet, petname
FROM testtable01
WHERE petname like '%John%'