MySQL 选择一个表中的所有项目并加入另一个表，允许空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5265350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting all items in one table and join with another table, allowing nulls
提问by Genadinik
I have a requirement to select all values from the table birds (basically all birds), and then joining with another table which tracks who likes that bird.
我需要从桌鸟（基本上是所有鸟）中选择所有值，然后加入另一个表来跟踪谁喜欢那只鸟。
So I want the query to return all birds, and ids of records where people like that bird. And if there is no record of anyone liking this bird, then that field should be null.
所以我希望查询返回所有鸟类，以及人们喜欢那只鸟的记录的 ID。如果没有任何人喜欢这只鸟的记录，那么该字段应该为空。
My current query isn't getting the nulls. Here it is:
我当前的查询没有得到空值。这里是：
select bird_name, member_id
from birds
right join bird_likes on birds.bird_id = bird_likes.bird_id
where member_id = 2 ;
What could I do to make sure each row in the birds table is getting displayed once?
我该怎么做才能确保鸟类表中的每一行都显示一次？
回答by krtek
you must use left joininstead of right join
你必须使用left join而不是right join
The different joins
不同的加入
inner join: keep only the rows where there's data in both table
inner join: 只保留两个表中有数据的行
left join: keep all the rows of the left table and add what is possible from the right one
left join: 保留左表的所有行，并从右表添加可能的行
right join: keep all the rows of the right table and add what is possible from the left one
right join: 保留右表的所有行并从左表添加可能的行
The left table is always the table we already have and the right table is the one we are joining with.
左表始终是我们已有的表，而右表是我们要加入的表。
For the record, there is also a cross joinwhich joins each row in the left table with each row in the right table, but this one isn't used very often.
作为记录，还有一个cross join将左表中的每一行与右表中的每一行连接起来，但这个不常用。
I hope all this is now clearer for you :)
我希望这一切现在对你来说更清楚:)
Corrected query
更正的查询
select bird_name, member_id
from birds
left join bird_likes on birds.bird_id = bird_likes.bird_id
where member_id = 2;
Be aware that this assumes that the column member_idis in the bird table, otherwise you can keep the condition like this :
请注意，这假定该列member_id在 Bird 表中，否则您可以保持这样的条件：
select bird_name, member_id
from birds
left join bird_likes on
birds.bird_id = bird_likes.bird_id and
bird_likes.member_id = 2;
回答by Joe Phillips
SELECT bird_name, member_id
FROM birds
LEFT JOIN bird_likes ON birds.bird_id=bird_likes.bird_id AND member_id=2
回答by RDL
You want to use left outer joinin this case
你想left outer join在这种情况下使用
select bird_name, member_id
from birds
left outer join bird_likes on birds.bird_id = bird_likes.bird_id
where member_id = 2;
This will return all bird names and 'null' for ones with empty likes.
这将返回所有鸟名，对于空喜欢的鸟名和“空”。
回答by ctcherry
Change your RIGHT JOIN to a LEFT JOIN, that will bring in all birdsrecords whether or not they have a relationship to bird_likes
将您的 RIGHT JOIN 更改为 LEFT JOIN，这将引入所有birds记录，无论它们是否与bird_likes
