SQL joining three tables, join precedence

All kinds of outer and normal joins are in the same precedence class and operators take effect left-to-right at a given nesting level of the query. You can put the join expression on the right side in parentheses to cause it to take effect first. Remember that you will have to move the ON clauses around so that they stay with their joins—the join in parentheses takes its ON clause with it into the parentheses, so it now comes textually before the other ON clause which will be after the parentheses in the outer join statement.

(PostgreSQL example)

In SELECT * FROM a LEFT JOIN b ON (a.id = b.id) JOIN c ON (b.ref = c.id);

the a-b join takes effect first, but we can force the b-c join to take effect first by putting it in parentheses, which looks like:

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Often you can express the same thing without extra parentheses by moving the joins around and changing the direction of the outer joins, e.g.

SELECT * FROM b JOIN c ON (b.ref = c.id) RIGHT JOIN a ON (a.id = b.id);

When you join the third table, your first query

SELECT
        *
    FROM 
        R
    JOIN    S ON (S.id = R.fks)

is like a derived table to which you're joining the third table. So if R JOIN S produces no rows, then joining P will never yield any rows (because you're trying to join to an empty table).

So, if you're looking for precedence rules then in this case it's just set by using LEFT JOIN as opposed to JOIN.

However, I may be misunderstanding your question, because if I were writing the query, I would swap S and R around. eg.

SELECT
        *
    FROM 
        S
    JOIN    R ON (S.id = R.fks)