# Joining 2 tables to see matching values and non-matching ones

Hi everyone,

How can I join 2 tables and then get a result of how many of the values can and cannot be found in other table?:

I have Table A with values (main table): 1, 1, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7, 8,
I have Table B with values (sub table): 2, 2, 4, 8, 8, 9, 10

I'd like to get 2 numbers:
1: How many distinct values of Table B can be found in Table A?
Desired result: 3 (Because value "2" appears in Table A, value "4" appears in Table A and value "8" appears in Table A)
2: How many distinct values of Table B cannot be found in Table A? Desired result: 2 (Because value "9" and value "10" does not appear in Table A)

Thank you!

Maybe also another addition, the column name for Table A is "TabACol" and for Table B it's "TabBCol".

hi hope this helps

creata data script

/*
create table #main ( number int )
create table #sub ( number int )
insert into #sub select 2
insert into #sub select 2
insert into #sub select 4
insert into #sub select 8
insert into #sub select 8
insert into #sub select 9
insert into #sub select 10

insert into #main select 1
insert into #main select 1
insert into #main select 1
insert into #main select 2
insert into #main select 3
insert into #main select 3
insert into #main select 3
insert into #main select 4
insert into #main select 5
insert into #main select 5
insert into #main select 6
insert into #main select 7
insert into #main select 8
*/

``````select
distinct b.number
from
#sub b
join
#main a
on a.number = b.number

select
distinct b.number
from
#sub b
left join
#main a
on a.number = b.number
where
a.number is null
``````