We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Full Outer Join not working

Comments

2 comments

  • Avatar
    Adrian Williams

    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
  • Avatar
    Adrian Williams

    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.