Right Outer Join to Left Outer join

Question

I have a query in sqlserver that needs to be translated to sqlite3, but this query uses "Right outer join", the problem is that sqlite doesn't support this operator yet.

how can I translate this query to use only "left outer join" instead of "right" outer join?

SELECT  *
FROM    P RIGHT OUTER JOIN
        CL RIGHT OUTER JOIN
        C LEFT OUTER JOIN
        CC ON C.IDC = CC.RIDC ON 
        C.IDC = C.RIDCL ON P.IDP = C.RIDP

Thanks.

PS: I'm having trouble also with the sqlite joins precedence and associativity, don't know how it may alter the final result by reordering the tables.

similar question http://stackoverflow.com/questions/9147025/sqlite3-simulate-right-outer-join-with-left-joins-and-union — Leo, Jan 31 '14 at 16:53

score 2 · Accepted Answer · answered Jan 31 '14 at 16:55

2

In this query, the table c is in the middle. So that is driving the query.

Your on conditions are in strange places. I am guessing this is what you mean:

SELECT  *
FROM    C LEFT OUTER JOIN
        CC
        ON C.IDC = CC.RIDC LEFT OUTER JOIN
        P
        ON P.IDP = C.RIDP LEFT OUTER JOIN
        CL
        ON CL.IDC = C.RIDCL

answered Jan 31 '14 at 16:55

Gordon Linoff

1,242,037
58
646
786

What do you mean by "driving the query" by being in the middle? – danielrvt Jan 31 '14 at 16:57
1

If you drew a chart of the query. Drawing arrows from the tables to show the 'outerness' of the joins. The 'C' table would be in the middle with all the arrows pointing out from it. – jerrylagrou Jan 31 '14 at 17:00

score 1 · Answer 2 · answered Jan 31 '14 at 17:24

An additional remark on the join syntax.

Most people write

Tab1 JOIN Tab2 ON ... JOIN Tab3 ON ... JOIN Tab4 ON ...

probably because this is the "natural" way, one table after the other.

But your joins use the "other" syntax i usually try to avoid.

Tab1 JOIN Tab2 JOIN Tab3 JOIN Tab4 ON ... ON ... ON ...

Logically joins are processed in the order of ON, so adding parens results in:

( P
  RIGHT OUTER JOIN
   ( CL
     RIGHT OUTER JOIN
      ( C
        LEFT OUTER JOIN
        CC
        ON C.IDC = CC.RIDC
      ) p
     ON C.IDC = C.RIDCL
   ) 
  ON P.IDP = C.RIDP
)

Rewriting it results in a join order C-CC-CL-P. This is not the C-CC-P-CL order Gordon used, but in your case this doesn't matter, because C is the main table :-)

Right Outer Join to Left Outer join

2 Answers2