SQLazy：Merge Multiple Tables into Single Rows by Common ID

Problem Description Merge multiple structurally similar tables with different column names into a wide table using full outer joins by common ID. Four tables have similar structures, each with two fields. The fields have the same meaning but different names (id, id2, id3, id4 all represent ID). The goal is to merge the four tables into single rows by ID, with each ID appearing in exactly one row. When an ID is absent in a table, the corresponding columns take NULL. Source Data T1 table: T2 table: T3 table: T4 table: * Expected Result * For example, ID=555 appears in both T1 and T2 but not in T3 or T4, so id, colA, id2, colB have values, while id3/colC/id4/colD are NULL. ID=222 only appears in T3, so only id3 and colC have values; all other columns are NULL. ID=10 appears in T2 and T4 but not in T1 or T3, so id2, colB, id4, colD have values; all other columns are NULL. SQLazy Step-by-Step Implementation Core Idea: First use derive to unify the ID column names of each table to ID_main, making subsequent merging easier. Then start from the first table and perform full outer joins one by one: use join to full outer join the current result with the next table on ID_main, then use derive and nvl to merge the new ID into the ID_main column, appending tables one by one to get the final result. [ Click to run this example online ] The steps are explained below. Steps 1-4: Unify ID Column Names Across Tables derive id as ID_main, id, colA Use derive on T1-T4 to rename their respective ID column names (id/id2/id3/id4) uniformly to ID_main. Step 5: Full Outer Join T1 and T2 join ID_main; with t2; ID_main; take id2, colB; full Use the join function to full outer join t1 and t2 on ID_main. Step 6: Merge NULLs in ID Column derive nvl(ID_main, id2) as ID_main, id, colA, id2, colB If a record comes from t2 but is not present in t1, its ID_main is NULL. This step assigns t2.id2 to ID_main in such records, ensuring the ID_main column always has a value. Different SQL implementations u