sqloracle-databasefull-outer-join

Can I avoid COALESCE during FULL OUTER JOIN?


In queries where I compare data between 2 tables, I often use combination of COALESCE and FULL OUTER JOIN to display records available only in 1 of the tables.

Can this be done with less syntactical sugar? (I do not mean replacing COALESCE with NVL or such.)

WITH Dataset1 AS (
    SELECT
        id,
        SUM(amount) AS amount
    FROM
        table1
    GROUP BY
        id
),

Dataset2 AS (
    SELECT
        id,
        SUM(amount) AS amount
    FROM
        table2
    GROUP BY
        id
)

SELECT
    COALESCE(d1.id, d2.id) AS ID,
    COALESCE(d1.amount, 0) AS D1_AMOUNT,
    COALESCE(d2.amount, 0) AS D2_AMOUNT,
    COALESCE(d1.amount, 0) - COALESCE(d2.amount, 0) AS DELTA
FROM
    Dataset1 d1
FULL OUTER JOIN
    Dataset2 d2 c ON
        d2.id = d1.id
WHERE
    ABS(COALESCE(d1.amount, 0) - COALESCE(d2.amount, 0)) >= 5
ORDER BY
    ID

Solution

  • The way you have written the query is the proper way to do it and COALESCE is necessary for it to work properly, as any column can be null in a full outer join.

    I would make it a habit, though, to name operation results different from the column. Here you work on a column called amount and name the result amount:

    SUM(amount) AS amount
    

    I would rather make this

    SUM(amount) AS total
    

    And as to the ID: You can use USING instead of ON to avoid having to work with COALESCE on the joined-on column(s). USING is very typical for full outer join queries and becomes even more handy when outer joining more than one table on the same column.

    WITH
      dataset1 AS (SELECT id, SUM(amount) AS total FROM table1 GROUP BY id),
      dataset2 AS (SELECT id, SUM(amount) AS total FROM table2 GROUP BY id)
    SELECT
        id,
        COALESCE(d1.total, 0) AS d1_amount,
        COALESCE(d2.total, 0) AS d2_amount,
        COALESCE(d1.total, 0) - COALESCE(d2.total, 0) AS delta
    FROM dataset1 d1
    FULL OUTER JOIN dataset2 d2 USING (id)
    WHERE ABS(COALESCE(d1.amount, 0) - COALESCE(d2.amount, 0)) >= 5
    ORDER BY id;