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:

T3 table:

*Expected Result

*

[

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.

Steps 7-8: Append T3

join ID_main; with t3; ID_main; take id3, colC; full

derive nvl(ID_main, id3) as ID_main, id, colA, id2, colB, id3, colC

Repeat the join + derive pattern to full outer join T3 into the current result. After appending each table, use ifn to merge the new ID into the unified ID_main column.

Generated SQL

After confirming the above steps, the SQLazy compiler automatically generates native SQL (SQL Server syntax):

WITH t1 AS (

        SELECT id AS ID_main, id, colA

        FROM T1

    ),

    t2 AS (

        SELECT id2 AS ID_main, id2, colB

        FROM T2

    ),

    r1 AS (

        SELECT t1.ID_main, t1.id, t1.colA, t2.id2, t2.colB

        FROM t1

            FULL JOIN t2 ON ID_main = t2.ID_main

    ),

    r1b AS (

        SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id2 AS VARCHAR(10)), '')) AS ID_main

            , id, colA, id2, colB

        FROM r1

    ),

    t3 AS (

        SELECT id3 AS ID_main, id3, colC

        FROM T3

    ),

    r2 AS (

        SELECT r1b.ID_main, r1b.id, r1b.colA, r1b.id2, r1b.colB

            , t3.id3, t3.colC

        FROM r1b

            FULL JOIN t3 ON ID_main = t3.ID_main

    ),

    r2b AS (

        SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id3 AS VARCHAR(10)), '')) AS ID_main

            , id, colA, id2, colB, id3

            , colC

        FROM r2

    ),

    t4 AS (

        SELECT id4 AS ID_main, id4, colD

        FROM T4

    ),

    r3 AS (

        SELECT r2b.ID_main, r2b.id, r2b.colA, r2b.id2, r2b.colB

            , r2b.id3, r2b.colC, t4.id4, t4.colD

        FROM r2b

            FULL JOIN t4 ON ID_main = t4.ID_main

    )

SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id4 AS VARCHAR(10)), '')) AS ID_main

    , id, colA, id2, colB, id3

    , colC, id4, colD

FROM r3

SQLazy lets you describe logic in business language instead of writing nested SQL queries. SQLazy's step-by-step computation breaks multi-table merging into independent steps such as unifying column names, performing full outer joins one table at a time, and merging IDs - each step can be independently verified for intermediate results. The join function, combined with derive and nvl, flexibly handles post-merge operations such as renaming columns and merging NULL values. This strategy of appending tables one by one is much clearer and more maintainable than writing deeply nested FULL JOIN + COALESCE queries in SQL.

Official Links

SQLazy Online Experience: