KB-93711: Return data from different tables with FULL OUTER JOIN.

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 column (TimeStamp), although their other columns are not the same. To do so, I’ve created an SQL 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, and then the next two columns. However, when I try to join the data from both tables, the E3TimeStamp column returns null values in a few fields.

In the figures below, you can see the tables used in this example:

Diferentes tabelas do Elipse E3 para serem reunidas

SQL syntax:

SELECT Tbl1.E3TimeStamp AS DataHora, Tbl1.Campo1,Tbl2.Campo2
FROM Tbl1
FULL OUTER JOIN Tbl2
ON Tbl1.E3TimeStamp = Tbl2.E3TimeStamp
ORDER BY DataHora ASC

The result from merging both tables via FULL OUTER JOIN function is seen below:

campos nulos são retornados

Why does this happen? How can I fix this?

Solution:

First of all, we  must make clear that using  FULL OUTER JOIN function to merge data from tables with different values in  E3TimeStamp will result in null fields. In order to have this function fulfilling its role properly, the E3TimeStamp fields in Table 1 and Table 2 must be an exact match.

Tip: FULL OUTER JOIN and FULL JOIN are the same function.

Thus, to fix this situation, you’ll need to replace the E3TimeStamp 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 function. This function returns a specified value if the expression is NULL; if the expression is NOT NULL, this function returns the expression.

The syntax is:

ISNULL (expression, value)

The expression parameter tests whether is NULL. The value parameter is the value to return if expression is NULL .

 

Example of  SQL syntax:

SELECT ISNULL (Tbl1.E3TimeStamp,Tbl2.E3TimeStamp) AS DataHora, Tbl1.Campo1,Tbl2.Campo2
FROM Tbl1
FULL OUTER JOIN Tbl2
ON Tbl1.E3TimeStamp = Tbl2.E3TimeStamp
ORDER BY DataHora ASC

 

This will result in a table similar to the one in the figure below:

como unir dados de diferentes tabelas com uma coluna em comum
Este artigo foi útil? Was this post helpful?
Yes0
No0

Leave a Reply

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