Table A is a list of IDs.
ID
---
1
5
9
...
Table B has IDs and another Y/N value.
ID Value
----------
1 0
2 1
3 0
...
I'd the count of Value for IDs that are:
i) both in tables A & B,
ii) in A but not B,
iii) in B but A.
One way is to use union:
SELECT Value, COUNT(*) AS count FROM tableA INNER JOIN tableB USING (id) GROUP BY Value
UNION
SELECT Value, COUNT(*) AS count FROM tableA RIGHT JOIN tableB USING (id) WHERE tableA.id IS NULL GROUP BY Value
Is there a better way?
You want ids from both tables, which reads like a full join (if your database, which you did not tag, does support it). We can then compute the counts with conditional expressions.
select
sum(case when t1.id is not null and t2.id is not null then 1 else 0 end) cnt_in_both_tables,
sum(case when t1.id is not null and t2.id is null then 1 else 0 end) cnt_in_t1_only,
sum(case when t1.id is null and t2.id is not null then 1 else 0 end) cnt_in_t2_only
from mytable1 t1
full join mytable2 t2 on t1.id = t2.id
This assumes that ids are unique in both table, as shown in your sample data - otherwise we would need to deduplicate both datasets first.
In databases that do not support full joins, we would likely emulate it with union and subqueries.