JOIN priority for multiple columns using OR

Question

I am trying to track the source of comments on a blog, and my question is regarding JOINING priority in multiple columns using the OR operator.

Comment table:

  Column | Type --------------------+------------------------- id | integer text | character varying(1024) session_source_id | integer user_source_id | integer 

If I have a request like this:

 SELECT s.type, COUNT(c.id) FROM comments c LEFT JOIN sources s ON (c.session_source_id = s.id OR c.user_source_id = s.id) GROUP BY 1; 

There are situations when we know the source of the user, but NOT the source of the session (which is why I used OR ), but I want to prioritize session_source_id if we also have user_source_id, and these two identifiers are different.

(there are also situations where we do not know either the sources, or both columns are equal to zero, therefore LEFT JOIN )

Does this request prioritize JOIN on session_source_id since it is listed first in join OR ? How do Postgres handle OR conditionals in JOIN statements?

Answer 1

Posting a GarethD comment as an answer and a Wiki so that it is not lost:

Try COALESCE instead of OR ...

 LEFT JOIN sources s ON s.id = COALESCE(c.session_source_id, c.user_source_id) 

Answer 2

Using OR will not prioritize conditions; if both conditions are true, both records will be returned.
If you want any priority, you can use CASE EXPRESSION , which violates the moment the condition is met:

 SELECT s.type, COUNT(c.id) FROM comments c LEFT JOIN sources s ON (CASE WHEN c.session_source_id = s.id THEN 1 WHEN c.user_source_id = s.id THEN 1 ELSE 0 END = 1) GROUP BY 1;