postgresqljoinderived-tableinline-view

How to do outer join with inline view (select in from clause) in Postgresql

I have a query similar to this simplified example:

select u.id, sq.score
from usr as u,
  (select user_id, score FROM score WHERE bar = ?) as sq
where u.id = sq.user_id

I would like the join (u.id = sq.user_id) to be an outer join. I can't figure out how to use JOIN in the from clause with a 'select' like this.

I know I could do this example without having to use a select in the from clause but thats not what I need in my application.

Solution

  • Something like this:

    select u.id, sq.score
from usr as u 
left join (    -- or right/full join as per your needs
    select user_id, score FROM score WHERE bar = ?
) as sq
on u.id = sq.user_id