Right join does not return null values

Question

My tables are:

allowed(resourceid, personid)
person(personid, email)

I want to print out whether a person has the right to access a resource, lets say with the resourceid = 2. The result should be:

personright(personid, email, resourceid)

and resourceid should be null if the person is not allowed to access resource 2. If the person is allowed to access 2, resourceid should be 2.

So I expect that everytime my query is executed that the whole user list is printed out.

I had a working solution using a subquery, but I want to do this with a join.

select person.personid, person.email, allowed.resourceid
from person 
right join allowed on(person.personid = allowed.personid)
where (allowed.resourceid IS NULL OR allowed.resourceid = 2);

Why does this not work?

Answer 1

Based on your description of problem - it should be left join here, not right join.

select person.personid, person.email, allowed.resourceid
from person 
    left join allowed on person.personid = allowed.personid and allowed.resourceid = 2

Also note I've moved allowed.resourceid = 2 condition from where clause to the join condition. Thus if there are no matching records in allowed table having resourceid = 2 and personid equals to appropriate personid from person table - you will get null as allowed.resourceid exactly as it was required.