SQL joining three tables, join precedence

Question

I have three tables: R, S and P.

Table R Joins with S through a foreign key; there should be at least one record in S, so I can JOIN:

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

If there's no record in S then I get no rows, that's fine.

Then table S joins with P, where records is P may or may not be present and joined with S.

So I do

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

What if I wanted the second JOIN to be tied to S not to R, like if I could use parentheses:

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

Or is that already a natural behaviour of the cartesian product between R, S and P?

Answer 1

The second join is tied to S as you explicity state JOIN P ON (P.id = S.fkp) - no column from R is referenced in the join.

Answer 2

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)

Answer 3

There is no precedence to joins, it is associative operation. The old comma separated syntax made this clear, the new one (which is presumably cooked to mimic relational algebra) is confusing. Outer joins (used alone or together with regular joins), however, are not associative.