Trying to find intersection of two lists of identities (lists are of different length), but getting `column id is ambiguous`?

Question

I am trying to find the intersection of the following two queries:

select distinct(id) from sval where val = 'True' and fid = 4044;

select distinct(id) from ival where fid = 3994 and val=0;

They both return a list of unique id's.

I find the intersection (i.e. the overlapping values) by doing:

select distinct(id) 
from sval 
where (val = 'True' and fid = 4044) 
  and exists(
    select distinct(id) from ival 
    where fid = 3994 and val=0
  );

Is the above correct?

The odd thing is that if I do a count for the first query and one for the second query, I get row counts of252290 and 22268.

And when I do a count of the third row, you would think that at most it returns 22268, however, I get 252290, any ideas why this is?

Answer 1

your exists would not work, you need a correlated subquery for that

Here are two possibilities for an query

select distinct(id) 
from sval 
where (val = 'True' and fid = 4044) 
  and id IN (
    select distinct(id) from ival 
    where fid = 3994 and val=0
  );

or

select distinct(id) 
from sval 
where (val = 'True' and fid = 4044) 
  and exists(
    select 1 from ival 
    where fid = 3994 and val=0 AND id = sval.id
  );