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:
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:
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:
