Two COUNT() columns in a SQL query, one for IN/INNER JOIN and one for NOT IN/LEFT JOIN

Question

Is it possible to create a query where you can have two COUNT() columns where one column is for something like IN() and another one for something like NOT IN() and also just by using JOIN?

For instance, I have a table tbl_a and tbl_b. In a single query I want to have 1 COUNT() column for rows existing in both tables. And another COUNT() column for rows not existing in tbl_b but existing in tbl_a.

sample in **tbl_a**:
|trans_id|amount |
|--| --- | --- |
|001 | 100.00 |
|002 |250.00|
|003 |300.00 |

sample in **tbl_b**:
|trans_id|ticket_no |
|--| --- | --- |
|001 |X0001|
|002 |X0002|


and my expected output:
|in_a_and_b|not_in_b|
|--| --- | --- |
|2|1|

Answer 1

Assuming you have a key that matches rows in the tables uniquely, you can use:

select count(*) as in_a,
       count(b.col) as in_a_and_b,
       (count(*) - count(b.col)) as in_a_not_b
from tbl_a a left join
     tbl_b b
     on a.col = b.col