PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to apply an inner join only to a subset of a table

Say I have two tables, Students and Scores:

Students
ID|Name|Gender
1|Ryan|M
2|Joanna|F
3|Shelly|F
4|James|M
5|Ron|M

Scores
ID|StudentID|Score
1|1|85
2|3|71
3|5|92
4|3|55
5|4|63

I want to get the names of all students, but I only want passing students for the boys. The result that I want is:

Ryan
Joanna
Shelly
Ron

The best I got is:

select name
from students
left join scores on (students.id=scores.student_id
      and students.gender='M'
      and scores.score > 70)
where (students.gender='M' and score is not null)
      or students.gender != 'M'

but here I’m checking the gender three times. Is there a way to do this more optimally? Is it possible to apply the gender filter once, and the score filter once?

In my mind, this operation should be equivalent to applying an inner join only to the subset of a table. But that is not the case in application. Doing so would give you back the joined subset, NOT a join applied to a subset of the entire table, as I want here.

TIA!

Tags:

2

Answers


  1. 0

    Since you only require names from the students table you can use exists

    select name
from students s
where s.gender = 'f' or (
    s.gender = 'm' and exists (
    select * from scores sc
    where sc.StudentId = s.Id and s.Score > 70
  )
);
    Login or Signup to reply.
  2. 0

    The question is not 100% clear to me, but based on what I understood I see two ways to do this:

    select distinct name
from students st
inner join scores sc on st.id=sc.student_id
      and (st.gender='F' OR sc.score > 70)

    or

    select distinct name
from students st
left join scores sc on st.id=sc.student_id
where st.gender='F' OR sc.score > 70
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search