Map ids from 2 tables and find duplicate records-CodePudding

I have a 2 tables and I want to find which id's are duplicate:

create table main_pairs(
   pair_id         integer,
   pair_full_name  text
)


create table second_pairs(
   pair_id         integer,
   pair_full_name  text
)

I want to be sured that I don't have into the both tables records for table columns pair_id as duplicate records.

How I can check this with SQL query?

CodePudding user response：

You just join two tables by pair_id, rows that don't have counterparts will be filtered out:

SELECT *
  FROM main_pairs m
  JOIN second_pairs s ON (s.pair_id = m.pair_id);

CodePudding user response：

You can use intersect to find which pair_ids are duplicate between the two tables:

select pair_id from main_pairs
intersect
select pair_id from second_pairs;