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

```
```
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE lutser
        ( id INTEGER NOT NULL
        , category INTEGER NOT NULL
        );
INSERT INTO lutser(category, id) VALUES
(1,1100) ,(1,1200) ,(1,1300) ,(1,1500)
,(2,2000) ,(2,2100) ,(2,2300) ,(2,2500)
,(1,3500) -- added these
,(2,3500)
        ;

```

These queries construct a "bit mask" 1 for category==1, 2 for category==2, and add them up. So the mask is 3 when the id is present in both sets, 1 when only in the first set, and 2 when only in the second set. The outer join + coalesce do the trick here.

```
        --
        -- CTE version
        --
WITH flags AS (
        WITH one AS ( SELECT category AS flag , id FROM lutser WHERE category = 1)
        , two AS ( SELECT category AS flag , id FROM lutser WHERE category = 2)
        SELECT COALESCE(one.flag, 0) + COALESCE(two.flag, 0) AS flag
        FROM one
        FULL OUTER JOIN two ON two.id = one.id
        )
SELECT flag, COUNT(*)
FROM flags
GROUP BY flag;

        --
        -- Non-CTE version
        --
SELECT COALESCE(one.flag, 0) + COALESCE(two.flag, 0) AS flags
        , COUNT(*)
FROM (
        SELECT category AS flag , id
        FROM lutser WHERE category = 1
        ) one
FULL OUTER JOIN (
        SELECT category AS flag , id
        FROM lutser WHERE category = 2
        ) two ON two.id = one.id
GROUP BY flags;

```

Result (for both queries ;-):

```
 flags | count 
-------+-------
     1 |     4
     2 |     4
     3 |     1

```