outer-join

How do I convert a “legacy” left outer join statement in Oracle?

Use: SELECT a.AccountNo, a.ParcelNo, a.LocalNo, a.PrimaryUseCode, a.DefaultTaxDistrict, TRIM(g.Section), TRIM(g.Township), TRIM(g.Range) FROM tblAcct A LEFT JOIN tblAcctLegalLocation g ON g.accountno = a.accountno AND g.verstart <= ‘20100917999’ AND g.verend > ‘20100917999’ WHERE a.verstart <= ‘20100917999’ AND a.verend > ‘20100917999’ AND a.DefaultTaxDistrict=”2291″ AND SUBSTR(a.AccountNo,1,1) IN (‘R’, ‘I’) AND SUBSTR(a.ParcelNo,1,1) NOT IN (‘7’, ‘8’) AND a.AcctStatusCode IN (‘A’, ‘T’, … Read more

Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?

A subsequent inner join will only “essentially invalidate” an outer join if the inner join’s ON clause requires should-be-optional rows to be present. In such a case, reordering the join either won’t work or won’t help; rather, the only fix is to change the inner join to an appropriate outer join. So, for example, this … Read more

LINQ to SQL – Left Outer Join with multiple join conditions

You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax: from p in context.Periods join f in context.Facts on p.id equals f.periodid into fg from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty() where p.companyid == 100 select f.value Or you could use a subquery: from p in context.Periods … Read more

Oracle “(+)” Operator

That’s Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn’t standardize OUTER joins. The query would be re-written in ANSI-92 syntax as: SELECT … FROM a LEFT JOIN b ON b.id = a.id This link is pretty good at explaining the … Read more

LINQ – Full Outer Join

Update 1: providing a truly generalized extension method FullOuterJoin Update 2: optionally accepting a custom IEqualityComparer for the key type Update 3: this implementation has recently become part of MoreLinq – Thanks guys! Edit Added FullOuterGroupJoin (ideone). I reused the GetOuter<> implementation, making this a fraction less performant than it could be, but I’m aiming … Read more