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?

Andrey Korneyev · Accepted Answer · 2015-07-16T13:02:43.530

4

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.

edited Jul 16 '15 at 13:02

answered Jul 16 '15 at 12:55

Andrey Korneyev

26,353
15
70
71

1

@Babs it is not a typo, it was intended. See updated answer. – Andrey Korneyev Jul 16 '15 at 13:04
@AndyKorneyev oh now I see it! Thank you so much for your help. – 今天春天 Jul 16 '15 at 13:05

Right join does not return null values

1 Answers1