I have table A that looks like this :
| id | name |
|---|---|
| a1 | A1 |
| a2 | A2 |
| b2 | B2 |
and table B that looks like this:
| volume_id | volume_name |
|---|---|
| a1 | A1 |
| b1 | B1 |
| b2 | B2 |
I want to make a query (or multiple) that would give me the id (or volume_id as they represent the same thing ) that exists in table A but not table B and vice versa.
I am using psql as my postgres cli.
You can use full join:
select a.id, b.volume_id
from a full join
b
on a.id = b.volume_id
where a.id is null or b.volume_id is null;
This puts the results in separate columns, so you can see which is missing.
You can use a FULL JOIN which would display the values that are present in column A but not column B.
select t1.id, t2.volumeid from a as t1 full join b as t2 on t1.id=t2.volumeid;
As a side note, you can also use a LEFT JOIN in similar circumstances to accomplish this, but you would want to make sure that the column on the left contains all the values included in a and b, or you will find a situation whereby extra values from the table will not display if they are in the right-hand column.
This is not the case here, i.e. table a does not include the value b1, and therefore this is why you must use a full join in this particular example.
