outer-join

Top 1 with a left join

by

Use OUTER APPLY instead of LEFT JOIN: SELECT u.id, mbg.marker_value FROM dps_user u OUTER APPLY (SELECT TOP 1 m.marker_value, um.profile_id FROM dps_usr_markers um (NOLOCK) INNER JOIN dps_markers m (NOLOCK) ON m.marker_id= um.marker_id AND m.marker_key = ‘moneyBackGuaranteeLength’ WHERE um.profile_id=u.id ORDER BY m.creation_date ) AS MBG WHERE u.id = ‘u162231993’; Unlike JOIN, APPLY allows you to reference … Read more

LINQ to SQL – Left Outer Join with multiple join conditions

by

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

by

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

by

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