CODEWITHSUNDEEP
sqlpostgresql
cad
cad

Reputation: 337

how to select records which are not in my b table

I have table A with 100k records and another table B with 500k records .B should have all the 100k records from A with a key in both tables. how to write a query to find records which are missing in B that exists in A. it should not list all other 400k which is not in A.

Upvotes: 0

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you care about all the columns, you can use except:

select a.*
from a
except
select b.*
from b;

This assumes that the columns in the table are exactly the same, with compatible types, and defined in the same order. It is normally better to list the columns explicitly.

Upvotes: 0

isaace
isaace

Reputation: 3429

This should do it.

 select * from a 
 left join b on b.id = a.id
 where b.id is null

Upvotes: 1

Matt
Matt

Reputation: 14361

sounds like you can do it by comparing a key/id. So you could use an anti left join, not exists, in, or except. Here is 1 method that should preform well for you.

SELECT *
FROM
    TableA a
WHERE
    NOT EXISTS(SELECT 1 FROM TableB b WHERE a.Id = b.Id)

Upvotes: 0

Related Questions