{"id":9372,"date":"2019-11-13T15:03:27","date_gmt":"2019-11-13T18:03:27","guid":{"rendered":"http:\/\/kb.elipse.com.br\/en\/?p=9372"},"modified":"2019-11-13T15:03:27","modified_gmt":"2019-11-13T18:03:27","slug":"kb-93711-return-data-from-different-tables-with-full-outer-join","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/","title":{"rendered":"KB-93711: Return data from different tables with FULL OUTER JOIN."},"content":{"rendered":"
\n

Question:<\/b><\/h2>\n

How can I join data from two different tables<\/a> (on the same database) with FULL OUTER JOIN function? I need to join data from two tables that share a column (TimeStamp), although their other columns are not the same. To do so, I’ve created an SQL<\/a> code with FULL OUTER JOIN function, and I’d like to sort out the data in the table in the following order: first the TimeStamp<\/b>, and then the next two columns. However, when I try to join the data from both tables, the E3TimeStamp<\/strong> column returns null values in a few fields.<\/p>\n

In the figures below, you can see the tables used in this example:<\/p>\n<\/div>\n

<\/div>\n
\n
\"Diferentes<\/div>\n

SQL syntax:<\/p>\n

SELECT Tbl1.E3TimeStamp AS DataHora, Tbl1.Campo1,Tbl2.Campo2\r\nFROM Tbl1\r\nFULL OUTER JOIN Tbl2\r\nON Tbl1.E3TimeStamp = Tbl2.E3TimeStamp\r\nORDER BY DataHora ASC<\/pre>\n
The result from merging both tables via FULL OUTER JOIN function is seen below:<\/p>\n<\/div>\n
<\/div>\n
\n
\"campos<\/div>\n
Why does this happen? How can I fix this?<\/p>\n<\/div>\n
<\/div>\n
\n
Solution:<\/b><\/h2>\n
First of all, we\u00a0 must make clear that using\u00a0 FULL OUTER JOIN<\/a> function to merge data from tables with different values in\u00a0 E3TimeStamp <\/strong>will result in null fields. In order to have this function fulfilling its role properly, the E3TimeStamp<\/strong> fields in Table 1 and Table 2 must be an exact match.<\/p>\n
Tip<\/strong>: FULL OUTER JOIN and FULL JOIN are the same function.<\/p>\n
Thus, to fix this situation, you’ll need to replace the E3TimeStamp <\/b>values of Table 1 for the ones in Table 2. Once you do that, the values in the columns will match, and therefore the return values will not be null. To do so, use ISNULL<\/a> function. This function returns a specified value if the expression is NULL; if the expression is NOT NULL, this function returns the expression.<\/p>\n
The syntax is:<\/p>\n
ISNULL (expression, value)<\/pre>\n
The expression<\/em> parameter tests whether is NULL. The value<\/em> parameter is the value to return if expression<\/em> is NULL .<\/p>\n
 <\/p>\n
Example of\u00a0 SQL syntax:<\/p>\n<\/div>\n
\n
SELECT ISNULL (Tbl1.E3TimeStamp,Tbl2.E3TimeStamp) AS DataHora, Tbl1.Campo1,Tbl2.Campo2\r\nFROM Tbl1\r\nFULL OUTER JOIN Tbl2\r\nON Tbl1.E3TimeStamp = Tbl2.E3TimeStamp\r\nORDER BY DataHora ASC<\/pre>\n
 <\/p>\n
This will result in a table similar to the one in the figure below:<\/p>\n<\/div>\n
<\/div>\n
\"como<\/div>\n","protected":false},"excerpt":{"rendered":"
Question: How can I join data from two different tables (on the same database) with FULL OUTER JOIN function? I need to join data from two tables that share a…<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0},"categories":[743,730],"tags":[],"yoast_head":"\nKB-93711: Return data from different tables with FULL OUTER JOIN.<\/title>\n<meta name=\"description\" content=\"This article shows how to join data from different tables (on the same database) in Elipse E3 with FULL OUTER JOIN function and not return null values.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"KB-93711: Return data from different tables with FULL OUTER JOIN.\" \/>\n<meta property=\"og:description\" content=\"This article shows how to join data from different tables (on the same database) in Elipse E3 with FULL OUTER JOIN function and not return null values.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\" \/>\n<meta property=\"og:site_name\" content=\"Elipse Knowledgebase\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/elipsesoftware\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-13T18:03:27+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID5485\/tabelas.png\" \/>\n<meta name=\"author\" content=\"D\u00e9lio Damin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"D\u00e9lio Damin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\"},\"author\":{\"name\":\"D\u00e9lio Damin\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778\"},\"headline\":\"KB-93711: Return data from different tables with FULL OUTER JOIN.\",\"datePublished\":\"2019-11-13T18:03:27+00:00\",\"dateModified\":\"2019-11-13T18:03:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\"},\"wordCount\":326,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"articleSection\":[\"E3Query\",\"Elipse E3\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\",\"url\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\",\"name\":\"KB-93711: Return data from different tables with FULL OUTER JOIN.\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#website\"},\"datePublished\":\"2019-11-13T18:03:27+00:00\",\"dateModified\":\"2019-11-13T18:03:27+00:00\",\"description\":\"This article shows how to join data from different tables (on the same database) in Elipse E3 with FULL OUTER JOIN function and not return null values.\",\"breadcrumb\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\/\/kb.elipse.com.br\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"KB-93711: Return data from different tables with FULL OUTER JOIN.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/kb.elipse.com.br\/#website\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"name\":\"Elipse Knowledgebase\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/kb.elipse.com.br\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\",\"name\":\"Elipse Software\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"sameAs\":[\"http:\/\/www.facebook.com\/elipsesoftware\"],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"contentUrl\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"width\":161,\"height\":58,\"caption\":\"Elipse Software\"},\"image\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778\",\"name\":\"D\u00e9lio Damin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g\",\"caption\":\"D\u00e9lio Damin\"},\"url\":\"https:\/\/kb.elipse.com.br\/en\/author\/delio\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"KB-93711: Return data from different tables with FULL OUTER JOIN.","description":"This article shows how to join data from different tables (on the same database) in Elipse E3 with FULL OUTER JOIN function and not return null values.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/","og_locale":"en_US","og_type":"article","og_title":"KB-93711: Return data from different tables with FULL OUTER JOIN.","og_description":"This article shows how to join data from different tables (on the same database) in Elipse E3 with FULL OUTER JOIN function and not return null values.","og_url":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/","og_site_name":"Elipse Knowledgebase","article_publisher":"http:\/\/www.facebook.com\/elipsesoftware","article_published_time":"2019-11-13T18:03:27+00:00","og_image":[{"url":"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID5485\/tabelas.png"}],"author":"D\u00e9lio Damin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"D\u00e9lio Damin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#article","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/"},"author":{"name":"D\u00e9lio Damin","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778"},"headline":"KB-93711: Return data from different tables with FULL OUTER JOIN.","datePublished":"2019-11-13T18:03:27+00:00","dateModified":"2019-11-13T18:03:27+00:00","mainEntityOfPage":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/"},"wordCount":326,"commentCount":0,"publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"articleSection":["E3Query","Elipse E3"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/","url":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/","name":"KB-93711: Return data from different tables with FULL OUTER JOIN.","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/#website"},"datePublished":"2019-11-13T18:03:27+00:00","dateModified":"2019-11-13T18:03:27+00:00","description":"This article shows how to join data from different tables (on the same database) in Elipse E3 with FULL OUTER JOIN function and not return null values.","breadcrumb":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/kb.elipse.com.br\/en\/kb-93711-return-data-from-different-tables-with-full-outer-join\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/kb.elipse.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"KB-93711: Return data from different tables with FULL OUTER JOIN."}]},{"@type":"WebSite","@id":"https:\/\/kb.elipse.com.br\/#website","url":"https:\/\/kb.elipse.com.br\/","name":"Elipse Knowledgebase","description":"","publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kb.elipse.com.br\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/kb.elipse.com.br\/#organization","name":"Elipse Software","url":"https:\/\/kb.elipse.com.br\/","sameAs":["http:\/\/www.facebook.com\/elipsesoftware"],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/","url":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","contentUrl":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","width":161,"height":58,"caption":"Elipse Software"},"image":{"@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778","name":"D\u00e9lio Damin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g","caption":"D\u00e9lio Damin"},"url":"https:\/\/kb.elipse.com.br\/en\/author\/delio\/"}]}},"_links":{"self":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/9372"}],"collection":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/comments?post=9372"}],"version-history":[{"count":5,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/9372\/revisions"}],"predecessor-version":[{"id":9377,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/9372\/revisions\/9377"}],"wp:attachment":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/media?parent=9372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/categories?post=9372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/tags?post=9372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}