Forum Discussion

Harsha3's avatar
Harsha3
New Contributor III
5 years ago

Queries in merging two tables

Good Day,

We need to perform a simple activity where I used outer join and merge . The purpose of merge is to actually unite both the tables to proceed further . Since the entries present in the result table are not in source table , there are null entries which are expected . I would like to replace all the null values with the values of the present in the source table . I provide screenshot for better clarity.

Table 1: ( Source table )

Table 2: ( After performing join)

Merged result (Merging Table 1 and Table 2 ):

Expected Output:

--Wanted all the null values exactly to be replaced with the value present in the first row . Is there any way to do the same.

Thanks,
Harshaavrdhana

4 Replies

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    5 years ago

    @Harsha3

    Probably you can try with Outer join, putting ‘1’ for Left and Right Path.

    Regards,
    Spiro Taleski

    • ptaylor's avatar
      ptaylor
      Employee
      5 years ago

      Don’t use Outer when doing a 1=1 join. Always use Inner. The result will be the same, by definition, but the performance, both memory-wise and time-wise, will be better with Inner. This advice is based on the details of the implementation of each of these joins.

      • Harsha3's avatar
        Harsha3
        New Contributor III
        5 years ago

        Hi Ptylor,

        Thanks for the details it actually worked !.

        Thanks Taleski for the updates.

  • ptaylor's avatar
    ptaylor
    Employee
    5 years ago

    A Join with join type Merge doesn’t do a true join – it just merges documents from each input stream as your results show. But I’m confused by your example. Your Table 2 says “after performing join”. What does the data look like before performing the join that produced Table 2? Is there a value corresponding to User_Id from Table 1 that you could use for a true join? I’m guessing that a 1=1 is not what you really need – it will produce a full cross-product of both input streams. If you decide to use 1=1, set the join type to Inner, and arrange the Join so that the input with more documents (table 2) is the first input, and the input with a single document (table 1) is the second input.