0

I am dealing with a large dataset, and have run into some issues because of this.

I have a data set like:

Table1

    State    ID  Conditional_Value
     AZ       1         Yes
     AZ       2         Yes
     AZ       5         Yes

Table 2

    State    ID  Conditional_Value
     AZ       1         No
     AZ       3         No 
     AZ       7         No

What I need this to return is

   State    ID  Conditional_Value
     AZ       1         Yes
     AZ       2         Yes
     AZ       3         No
     AZ       5         Yes
     AZ       7         No

If there is a duplicate ID in table 2 take that ID's row from table 1 and select it.

I have tried

    Select State, ID, Conditional_Value 
    From Table1
    Union
    Select State, ID, Conditional_Value
    From Table2
    Where Table2.ID not in (select Table1.ID from Table1)

But with the large dataset I have, it is too expensive to run.

I have looked at questions about finding duplicates and deleting them and then inserting the non-duplicates into another table, but I need to compare the 2 separate tables as well.

If there is any more info needed, please let me know. Thanks!

5
  • Is there any index on table1 and table2?
    – McNets
    Commented Nov 26, 2018 at 15:46
  • I could refresh the tables with an index on the ID (which is actually a concatenate value between another id and date) if that helps.
    – Mike Push
    Commented Nov 26, 2018 at 15:49
  • 1
    IMHO it'd help knowing the exactly CREATE TABLE statement, index included.
    – McNets
    Commented Nov 26, 2018 at 15:51
  • I re-built the tables with indexes on a different key (the concatenate value) and it worked in under 7 seconds. Thanks @McNets
    – Mike Push
    Commented Nov 26, 2018 at 16:00
  • Why do a union when you are manually removing duplicates from the second query? Switching to a union all should help with performance.
    – Joe W
    Commented Nov 26, 2018 at 16:52

1 Answer 1

0

While you already seem to have resolved the problem to your satisfaction, the classical solution to this is to use a FULL OUTER JOIN:

SELECT COALESCE(t1.State, t2.State) as State
      ,COALESCE(t1.ID, t2.ID) as ID
      ,COALESCE(t1.Conditional_Value, t2.Conditional_Value) as Conditional_Value 
  FROM Table1 t1
         FULL OUTER JOIN Table2 t2 ON (    t1.State = t2.State
                                       AND t1.ID = t2.ID)

Note: code untested, and I haven't worked with teradata specifically, so there may be some syntax issues I can't predict. However, the basic commands and functions should be correct, from what I can see.

The code merges the two tables based on the State and ID values. The COALESCE functions ensure that whichever table has a value for a particular row, that's the value that will be used. Since we look at Table1 first, if both Table1 and Table2 have a value for Conditional_Value, the value from Table1 will be used.

1
  • thank you - I will give this a run as well - indexing alone worked on a smaller dataset, but when trying to run the larger dataset - I will try the coalesce function. Thanks for your time, everyone.
    – Mike Push
    Commented Nov 26, 2018 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.