Question: I have two tables, T1 and T2 as follow : 

```
CATEGORY     ID
1           1100
1           1200
1           1300
1           1500 
2           2000
2           2100
2           2300
2           2500

```

I need to know :

* how many rows are similar between T1 and T2 (same CATEGORY and ID)
* how many rows from T2 are not in T1
* how many rows from T1 are not in T2

I'm bunching my head on it since this morning, and tried to do that to get the similar rows:

```
select count(*) from T1, T2 WHERE 
T1.CATEGORY = T2.CATEGORY AND T1.ID = T2.ID; 

```

But I can't figure out how to get unique rows (only in T1 or T2).

Answer: 
Question 1

```
SELECT  COUNT(*) totalCount
FROM    T1 a 
        INNER JOIN T2 b
            ON a.Category = b.Category AND
                a.ID = b.ID

```

Question 2 (*use `LEFT JOIN`*)

```
SELECT  COUNT(*) totalCount
FROM    T2 a 
        LEFT JOIN T1 b
            ON a.Category = b.Category AND
                a.ID = b.ID
WHERE   b.Category IS NULL

```

Question 3 (*use `LEFT JOIN`*)

```
SELECT  COUNT(*) totalCount
FROM    T1 a 
        LEFT JOIN T2 b
            ON a.Category = b.Category AND
                a.ID = b.ID
WHERE   b.Category IS NULL

```
If you cannot assume that the rows are distinct, then you need to take a slightly different approach. Here is a method that answers all three questions at the same time, taking into account duplicate rows:

```
select (case when isT1 = 1 and isT2 = 0 then 'BOTH'
             when isT1 = 1 then 'T1-Only'
             else 'T2-Only'
        end) as WhereRow,
       count(*) as NumDistinctRows,
       sum(cnt) as NumTotalRows
from ((select category, id, count(*) as cnt, 1 as isT1, 0 as isT2
       from t1
       group by category, id
      ) union all
      (select category, id, count(*) as cnt, 0 as isT1, 1 as isT2
       from t2
       group by category, id
      )
     ) t
group by isT1, isT2

```