MySQL - SELECT IN子句与不匹配的元素在输出?

Question

SELECT name, age
FROM mytable
WHERE name IN ("Bob", "Rob", "Robert");

将只列出名称为"Bob"， "Rob"， "Robert"的行。

是否有可能获得一个输出像下面使用(My)SQL吗?

Bob, 23
Rob, NULL (meaning there is no Rob in the table)
Robert, 30

而不是通常的:

Bob, 23
Robert, 30

Answer 1

尝试使用以下代码来查找缺少的名称:

select * from mytable WHERE name IN ("Bob", "Rob", "Robert")
union
select "Bob", null from mytable where not exists (SELECT Name FROM Person where name in ("Bob"))
union
select "Rob", null from mytable where not exists (SELECT Name FROM Person where name in ("Rob"))
union
select "Rober", null from mytable where not exists (SELECT Name FROM Person where name in ("Robert"));