Mixing Left and right Joins? Why?
join, sql
Solution
One thing I would do is make sure you know what results you are expecting before messing with this. Wouldn't want to "fix" it and have different results returned. Although honestly, with a query that poorly designed, I'm not sure that you are actually getting correct results right now.
To me this looks like something that someone did over time maybe even originally starting with inner joins, realizing they wouldn't work and changing to outer joins but not wanting to bother changing the order the tables were referenced in the query.
Of particular concern to me for maintenance purposes is to put the ON clauses next to the tables you are joining as well as converting all the joins to left joins rather than mixing right and left joins. Having the ON clause for table 4 and table 3 down next to table 9 makes no sense at all to me and should contribute to confusion as to what the query should actually return. You may also need to change the order of the joins in order to convert to all left joins. Personally I prefer to start with the main table that the others will join to (which appears to be table2) and then work down the food chain from there.
Problem
Doing some refactoring in some legacy code I've found in a project. This is for MSSQL. The thing is, i can't understand why we're using mixed left and right joins and collating some of the joining conditions together. My question is this: doesn't this create implicit inner joins in some places and implicit full joins in others? I'm of the school that just about anything can be written using just left (and inner/full) or just right (and inner/full) but that's because i like to keep things simple where possible. As an aside, we convert all this stuff to work on oracle databases as well, so maybe there's some optimization rules that work differently with Ora? For instance, here's the FROM part of one of the queries: ``` FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.T2FK = Table2.T2PK LEFT OUTER JOIN Table3 RIGHT OUTER JOIN Table4 LEFT OUTER JOIN Table5 ON Table4.T3FK = Table5.T3FK AND Table4.T2FK = Table5.T2FK LEFT OUTER JOIN Table6 RIGHT OUTER JOIN Table7 ON Table6.T6PK = Table7.T6FK LEFT OUTER JOIN Table8 RIGHT OUTER JOIN Table9 ON Table8.T8PK= Table9.T8FK ON Table7.T9FK= Table9.T9PK ON Table4.T7FK= Table7.T7PK ON Table3.T3PK= Table4.T3PK RIGHT OUTER JOIN ( SELECT * FROM TableA WHERE ( TableA.PK = @PK ) AND ( TableA.Date BETWEEN @StartDate AND @EndDate ) ) Table10 ON Table4.T4PK= Table10.T4FK ON Table2.T2PK = Table4.T2PK ```