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
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;