Can I avoid COALESCE during FULL OUTER JOIN?

Question

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

Answer 1

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;

Answer 2

I don't know if this is better.

WITH DataSet(ID, D1_AMOUNT, D2_AMOUNT, DELTA) AS (
    SELECT ID, SUM(COALESCE(Amount,0)), 0, SUM(COALESCE(Amount,0))
    FROM table1
    GROUP BY ID
    UNION ALL
    SELECT ID, 0, SUM(COALESCE(Amount,0)), SUM(COALESCE(Amount,0))*-1
    FROM table2
    GROUP BY ID
),
DataSet2(ID, D1_AMOUNT, D2_AMOUNT, DELTA) AS (
    SELECT ID,SUM(D1_AMOUNT),SUM(D2_AMOUNT), SUM(DELTA) 
    FROM DataSet 
    GROUP BY ID
)
SELECT *
FROM Dataset2
WHERE ABS(DELTA)>5