my query:

select a.id, a.affiliation 
FROM public.affiliation AS a
WHERE NOT EXISTS (
  SELECT *
  FROM ncbi.affi_known1 AS b 
  WHERE  a.id = b.id
)
limit 5000

it returns:

as first row.

but

select * from ncbi.affi_known1 where id = 4683763

do return the data with id = 4683763

CodePudding user response：

Your understanding of how EXISTS works might be off. Your current exists query is saying that id 4683763 exists in the affiliation table, not the affi_known1 table. So, the following query should return the single record:

SELECT a.id, a.affiliation 
FROM public.affiliation a
WHERE a.id = 4683763;

CodePudding user response：

I am assuming the requirement is to fetch rows only when the id is not present in the second table, so you can try this

select a.id, a.affiliation 
FROM public.affiliation AS a
WHERE a.id NOT IN (
  SELECT id
  FROM ncbi.affi_known1 
)

CodePudding user response：

If id were an integer, your query would do what you want.

If id is a string, you could have issues with "look-alikes". It is very hard to say what the problem is -- there could be spaces in the id, hidden characters, or something else. And this could be in either table.

Assuming the ids look like numbers, you could filter "bad" ids out using regular expressions:

select id
from ncbi.affi_known1
where not id ~ '^[0-9]*$';