Having selected all the elements in one table and join the other table, allowing null

Question

I have a requirement to select all the values ​​from the bird table (mostly all birds), and then join another table that keeps track of who loves this bird.

Therefore, I want the query to return all the birds, and the identifiers of the records in which people liked this bird. And if there is no record to anyone who loves this bird, then this field should be zero.

My current request does not get zeros. Here he 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 can I do to make each row in the bird table appear once?

Thanks Alex

Answer 1

you should use left join instead of right join

Various associations

inner join : keep only rows where there is data in both tables

left join : save all rows of the left table and add what is possible on the right side

right join : save all rows of the right table and add what is possible on the left side

The left table is always the table that we already have, and the right table is the one we are connecting to.

There is also a cross join for the record, which joins each row in the left table with each row in the right table, but this one is not used very often.

I hope now all this will become clearer for you :)

Corrected Request

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

Remember that this assumes that the member_id column is in the bird table, otherwise you can save the condition as follows:

 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; 

Answer 2

 SELECT bird_name, member_id FROM birds LEFT JOIN bird_likes ON birds.bird_id=bird_likes.bird_id AND member_id=2 

Answer 3

Do you want to use left outer join in this case

 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 those who have empty sympathies.

Answer 4

Change your CORRECT JOINT to LEFT JOIN, which will bring all the bird_likes records, whether they are related to bird_likes