Sql query to get count of matched and not matched records

user_970826 0
Viewed 361

Please let me know how can we achieve this with the below scenario

I am having a table with columns

Table A

EmpId   Name  Contact
---------------------
100     AA    xxxxx
101     BB    yyyyy
102     rr    zzzzz
103     jj    1234
104     GG    6789

Table B

Id EmpId  Product
------------------
1  100     Sample1
2  100     Sample2
3  101     Sample1
4  103     Sample4

I need to get a count of columns from TableA present in Table B (distinct) and not present in Table B matching the EmpId column

I tried with the below approach

SELECT count(*) 
FROM Table A ta
WHERE ta.EmpId NOT IN (SELECT DISTINCT(tb.EmpId) 
                       FROM Table B tb)

I am getting a count of only which rows do not exist.

How can I write a single query to get the count of all rows matched and not matched?

Desired output:

Matched NotMatched
------------------
3         2
sql-server-2012 sql-server
1 Answers

Use a left join with aggregation:

SELECT
    COUNT(DISTINCT b.EmpId) AS Matched,
    COUNT(CASE WHEN b.EmpId IS NULL THEN 1 END) AS NotMatched
FROM TableA a
LEFT JOIN TableB b
    ON b.EmpId = a.EmpId;
user_18632290
Related