Simpler alternative to inner join

Question

Is it possible compare data from 2 tables in one table?

table 1

ID	Val
1	Ann
2	Ben

table 2

ID	Val
1	Ann
3	Cyc

output

ID	Val1	Val2	Match
1	Ann	Ann	1
2	Ben		0
3		Cyc	0

UNION stacks queries on top, so one would end up with 4 rows (1,2,1,3). And INNER JOIN may not be the best solution since we do not know which table is longer?

(SELECT t1.id, t1.val, new.sum_val, t1.val = new.sum_val as Match
FROM t1

INNER JOIN 
(SELECT t2.id, SUM(t2.val) as sum_val
FROM t2
WHERE t2.date = TODAY()
GROUP BY t2.ID) new on t1.ID = t2.ID

WHERE t1.date = TODAY())

Perhaps you're looking for a *full join* – Stu Jan 14 '22 at 16:00 — Stu, Jan 14 '22 at 16:00

score 1 · Accepted Answer · answered Jan 14 '22 at 16:21

You can use a full join. That's roughly a left and a right join in one. It retains all rows from both table. If there's a matching partner row, the partner rows are combined, if not the values for the partner row stay NULL.

SELECT coalesce(t1.id, t2.id) AS id,
       t1.val AS val1,
       t2.val AS val2,
       CASE
         WHEN t1.val = t2.val
               OR t1.val IS NULL
                  AND t2.val IS NULL THEN
           1
         ELSE
           0
       END AS match
       FROM "table 1" AS t1
            FULL JOIN "table 2" as t2
                      ON t2.id = t1.id;

Simpler alternative to inner join

1 Answers1