选择一个表中的所有项并与另一表联接，允许为空 [英] Selecting all items in one table and join with another table, allowing nulls

问题描述

我需要从表中的鸟(基本上是所有鸟)中选择所有值，然后与另一个跟踪谁喜欢那只鸟的表联接.

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.

因此，我希望查询返回所有鸟类，以及人们喜欢该鸟类的记录的ID.如果没有任何人喜欢这只鸟的记录，那么该字段应该为空.

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.

我当前的查询没有获取空值.在这里:

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 ;

如何确保Birds表中的每一行都显示一次?

What could I do to make sure each row in the birds table is getting displayed once?

推荐答案

您必须使用left join而不是right join

inner join:仅保留两个表中都有数据的行

inner join : keep only the rows where there's data in both table

left join:保留左侧表的所有行，并添加右侧表中可能的内容

left join : keep all the rows of the left table and add what is possible from the right one

right join:保留右侧表的所有行，并添加左侧表中所有可能的行

right join : keep all the rows of the right table and add what is possible from the left one

左边的表始终是我们已经拥有的表，右边的表是我们正在连接的表.

The left table is always the table we already have and the right table is the one we are joining with.

出于记录目的，还有一个cross join，它将左表中的每一行与右表中的每一行连接在一起，但是这种情况并不经常使用.

For the record, there is also a cross join which joins each row in the left table with each row in the right table, but this one isn't used very often.

我希望这一切对您来说都更清楚了:)

I hope all this is now clearer for you :)

select  bird_name, member_id 
from birds  
left join bird_likes on birds.bird_id = bird_likes.bird_id 
where member_id = 2;

请注意，这假定列member_id在鸟表中，否则您可以保留以下条件:

Be aware that this assumes that the column member_id is in the bird table, otherwise you can keep the condition like this :

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;

这篇关于选择一个表中的所有项并与另一表联接，允许为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！