{"id":4723,"date":"2008-02-12T11:47:00","date_gmt":"2008-02-12T11:47:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2008\/02\/12\/converting-sql-to-linq-part-9-full-outer-join-bill-horst\/"},"modified":"2024-07-05T14:33:26","modified_gmt":"2024-07-05T21:33:26","slug":"converting-sql-to-linq-part-9-full-outer-join-bill-horst","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/converting-sql-to-linq-part-9-full-outer-join-bill-horst\/","title":{"rendered":"Converting SQL to LINQ, Part 9: Full Outer Join (Bill Horst)"},"content":{"rendered":"

This post assumes you’ve read the previous posts<\/a> in this series.  <\/span>It makes the same assumptions as Part 8, and I will pick up where I left off, covering Full Outer Join.  <\/span>Once again, I recognize this isn’t an ideal situation, so if you’d like to see better support of Full Outer Join in a future VB release, you can file a suggestion from our product feedback<\/a> site.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

A Full Outer Join is basically like the union of the results of a Left Outer Join and Right Outer Join.  <\/span>Records are joined together based on common fields, and all records with no match are also displayed, whether in the “left” or “right” table.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

SQL<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT Contact.ContactName, Shipment.OrderDate<\/p>\n

<\/span><\/p>\n

FROM CustomerTable Contact<\/p>\n

<\/span><\/p>\n

FULL OUTER JOIN OrderTable Shipment<\/p>\n

<\/span><\/p>\n

   <\/span>ON Contact.CustomerID = Shipment.CustomerID<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Using the same data as my last post, this query would give me results like the following:<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

Results:<\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

ContactName     <\/span>OrderDate<\/p>\n

<\/span><\/b><\/p>\n

John Doe        <\/span>NULL<\/p>\n

<\/span><\/p>\n

Bill Horst      <\/span>3\/25\/1982<\/p>\n

<\/span><\/p>\n

Bill Horst      <\/span>3\/13\/2005<\/p>\n

<\/span><\/p>\n

Jane Doe        <\/span>9\/29\/2007<\/p>\n

<\/span><\/p>\n

NULL            <\/span>1\/31\/2008<\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Like Left and Right Join, Full Outer Join is not supported directly with LINQ, but you can write a query to produce the same results.  <\/span>Currently, the simplest way to do this is to concatenate three subqueries together.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

The first query provides the results where the “right” table has a null value.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

VB<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

Group<\/span> Join<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

  <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

  <\/span>Into<\/span> HaveMatch = Any() _<\/p>\n

<\/span><\/p>\n

Where<\/span> Not<\/span> HaveMatch _<\/p>\n

<\/span><\/p>\n

Select<\/span> Contact.ContactName, OrderDate = NothingDate<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Results:<\/p>\n

<\/span><\/p>\n

{ContactName = “John Doe”, OrderDate = Nothing}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

The second query provides the results of an Inner Join.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

VB<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

Join<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

  <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

Select<\/span> Contact.ContactName, Shipment.OrderDate<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Results:<\/p>\n

<\/span><\/p>\n

{ContactName = “Bill Horst”, OrderDate = #3\/25\/1982#}<\/p>\n

<\/span><\/p>\n

{ContactName = “Bill Horst”, OrderDate = #3\/13\/2005#}<\/p>\n

<\/span><\/p>\n

{ContactName = “Jane Doe”, OrderDate = #9\/29\/2007#}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

The third query provides the results where the “left” table has a null value.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

VB<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

Group<\/span> Join<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

  <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

  <\/span>Into<\/span> HaveMatch = Any() _<\/p>\n

<\/span><\/p>\n

Where<\/span> Not<\/span> HaveMatch _<\/p>\n

<\/span><\/p>\n

Select<\/span> ContactName = NothingString, Shipment.OrderDate<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Results:<\/p>\n

<\/span><\/p>\n

{ContactName = Nothing, OrderDate = #1\/31\/2008#}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

When the results are put together, we get a full outer join.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

VB<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

(From<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

 <\/span>Group<\/span> Join<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

   <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

   <\/span>Into<\/span> HaveMatch = Any() _<\/p>\n

<\/span><\/p>\n

 <\/span>Where<\/span> Not<\/span> HaveMatch _<\/p>\n

<\/span><\/p>\n

 <\/span>Select<\/span> Contact.ContactName, OrderDate = NothingDate) _<\/p>\n

<\/span><\/p>\n

 <\/span>.Concat(From<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

         <\/span>Join<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

         <\/span>  <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

         <\/span>Select<\/span> Contact.ContactName, Shipment.OrderDate) _<\/p>\n

<\/span><\/p>\n

         <\/span>.Concat(From<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

           <\/span>      <\/span>Group<\/span> Join<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

                   <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

                   <\/span>Into<\/span> HaveMatch = Any() _<\/p>\n

<\/span><\/p>\n

                 <\/span>Where<\/span> Not<\/span> HaveMatch _<\/p>\n

<\/span><\/p>\n

                 <\/span>Select<\/span> ContactName = NothingString, Shipment.OrderDate)<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Results:<\/p>\n

<\/span><\/p>\n

{ContactName = “John Doe”, OrderDate = Nothing}<\/p>\n

<\/span><\/p>\n

{ContactName = “Bill Horst”, OrderDate = #3\/25\/1982#}<\/p>\n

<\/span><\/p>\n

{ContactName = “Bill Horst”, OrderDate = #3\/13\/2005#}<\/p>\n

<\/span><\/p>\n

{ContactName = “Jane Doe”, OrderDate = #9\/29\/2007#}<\/p>\n

<\/span><\/p>\n

{ContactName = Nothing, OrderDate = #1\/31\/2008#}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Hopefully this is helpful to some.  <\/span>As noted above, I realize this is not an ideal way to implement a Full Outer Join.  <\/span>If you’d like to see support for a Full Outer Join with simple syntax in a future VB release, please file a suggestion from our product feedback<\/a> <\/span><\/p><\/p>\n","protected":false},"excerpt":{"rendered":"

This post assumes you’ve read the previous posts in this series.  It makes the same assumptions as Part 8, and I will pick up where I left off, covering Full Outer Join.  Once again, I recognize this isn’t an ideal situation, so if you’d like to see better support of Full Outer Join in a […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[20,195],"tags":[41,49,94,166],"class_list":["post-4723","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-converting-sql-to-linq","category-visual-basic","tag-bill-horst","tag-converting-sql-to-linq","tag-linqvb9","tag-vb2008"],"acf":[],"blog_post_summary":"

This post assumes you’ve read the previous posts in this series.  It makes the same assumptions as Part 8, and I will pick up where I left off, covering Full Outer Join.  Once again, I recognize this isn’t an ideal situation, so if you’d like to see better support of Full Outer Join in a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4723","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=4723"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4723\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=4723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}