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

Transact-SQL
1

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!

2

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

3

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

image
image223×684 3.54 KB

image