I have two identical tables:
A : id1, id2, qty, unit
B: id1, id2, qty, unit
The set of (id1,id2)
is identifying each row and it can appear only once in each table.
I have 140
rows in table A
and 141 rows
in table B
.
I would like to find all the keys (id1,id2) that are not appearing in both tables. There is 1 for sure but there can't be more (for example if each table has whole different data).
I wrote this query:
(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;
But it's not working. It compares the whole table where I don't care if qty
or unit
are different, I only care about id1,id2
.
If there are foreign keys between the tables, then you can find the relationship between them. To do this, you can call \d on a table and see the foreign keys associated with its columns.
To compare two tables by using a field as a criterion, you create a select query that includes both tables. You include the fields that you want to display, and you also include the field that corresponds to the field that you want to use as a criterion. You then create a criterion to compare the tables.
use a full outer join:
select a.*,b.*
from a full outer join b
on a.id1=b.id1 and a.id2=b.id2
this show both tables side by side. with gaps where there is an unmatched row.
select a.*,b.*
from a full outer join b
on a.id1=b.id1 and a.id2=b.id2
where a.id1 is null or b.id1 is null;
that will only show unmatched rows.
or you can use not in
select * from a
where (id1,id2) not in
( select id1,id2 from b )
that will show rows from a not matched by b.
or the same result using a join
select a.*
from a left outer join b
on a.id1=b.id1 and a.id2=b.id2
where b.id1 is null
sometimes the join is faster than the "not in"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With