Full Outer Join not working

Follow

Sumeet Soni

• October 15, 2019 08:55

After watching official Join Node video I understood the concept and tried Full Outer Join to get all the records from both the table. But I am getting 0 records as output. 

Just a concern:

Data3Sixty has changed the basic join concepts in database. One has to read the concepts given in Data3Sixty again as it is totally different from basic concepts we read in DBMS. The Left Inner Join is actually Left Join, Right Inner is actually Right Join in DBMS Concept. It is confusing at first.

0

• 

  Adrian Williams

  • October 15, 2019 14:34

  The Help documentation for the Join node describes the operation of the node in relation to the configuration of the node's JoinType property.

  The Join node is not intended to be a GUI-based SQL builder. It is designed to provide business users and data analysts with the means to easily correlate two data sets. As such, the naming of the node's join types is a simplification of the types supported by SQL. The output generated by a SQL JOIN expression is also affected by the presence of an associated WHERE clause. The node's UI controls do not provide explicit control over the records that are output (though you can implement the equivalent functionality in the ProcessRecords script). The JoinType property determines which records are output by the node and the Field List property grid provides control over which fields are output in those records.

  • The Inner join type will only output records where there is a match.
  • The Left join type only outputs records from the left input where there was no match - i.e. the left orphans
  • The Left Inner join type outputs records from the left input and the corresponding fields from the right input where there was a match 
  • The Right join type only outputs records from the right input where there was no match - i.e. the right orphans
  • The Right Inner join type outputs records from the right input and the corresponding fields from the left input where there was a match 
  • The Full Outer join type outputs records from the left where there was no match and records from the right where there was not match.

  The following image illustrates the equivalent join types for SQL and the Data3Sixty Analyze Join node.

  

   

  0

  Comment actions Permalink
• 

  Adrian Williams

  • October 15, 2019 16:20

  Here is an example data flow that shows the Full Outer join use case. The Join node outputs the Left orphan and Right orphan records.

   

   

  Attached files

  Join_Node_Example_4.3.2 - 15 Oct 2019.lna

   

  0

  Comment actions Permalink

Please sign in to leave a comment.