Simpler alternative to inner join
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())
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;