Querying Entity Model. Part 19 – Full Outer Join

There is a rarely used join in SQL as full outer join. It combines the intersection between two sets, and both set exception from left and right parts of the join. Full outer join might be used to merge data from source and target tables, so you would get intersecting rows to update, new rows to insert, and missing rows to delete.

This type of join also can be implemented by LINQ queries on Entity Framework in four steps:

  1. Left outer join
  2. Right outer join
  3. Concat both outer joins
  4. Get distinct result

Here is an abridged T-SQL code (I’ve deleted extra lines in SELECT clause for readability).

As you see, T-SQL code repeats the logic of LINQ. Comparison of execution plans shows that a native FULL OUTER JOIN has cost 36% against 64% of LINQ in my case. Maybe you can get a bigger difference.

And native T-SQL code is more elegant 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *