Finding Overlapping Entries Between Certain Columns Of Two Tables

So I’ve tried searching and reading through many other posts before making this one.

Suppose you have two tables — table A and table B — where table B has more columns than A. Both share 2 columns: ID, location.

How would you:
(1) find any ID’s that overlap between the two tables
(2) if ID’s overlap, check if location overlaps

Thanks in advance for your help!

I tried to left join the larger table to the smaller one using a subquery, and then find the distinct number of ID’s that appeared.

>Solution :

For determining which IDs are in both tables, simply JOIN them on id. Then for checking if the locations match, use a boolean or case expression.

create table table1 (
  id integer,
  location varchar(5)
  );
  
create table table2 (
  id integer,
  location varchar(5)
  );
  
insert into table1 values 
(1, 'AAA'), 
(2, 'BBB'), 
(3, 'CCC'), 
(4, 'DDD');

insert into table2 values 
(1, 'AAA'), 
(2, 'EEEE'), 
(4, 'DDD');

select 
 a.id, 
 a.location = b.location as loc_match
from table1 a
join table2 b
  on a.id = b.id;