Compare two MYSQL tables and return missing dates and group by column

I have two tables. I want to compare table A with B and get the missing date from with name of the user.

TABLE A

|----|----------|------------|
| 1  | king     | 2020-08-01 |
| 2  | king     | 2020-08-02 |
| 3  | queen    | 2020-08-01 |
| 4  | queen    | 2020-08-02 |
| 5  | rook     | 2020-08-03 |
| 6  | bishop   | 2020-08-01 |
| 7  | bishop   | 2020-08-01 |
| 8  | queen    | 2020-08-03 |

TABLE B

| id | working_date |
|----|--------------|
| 1  | 2020-08-01   |
| 2  | 2020-08-02   |
| 3  | 2020-08-03   |

EXPECTED OUTPUT

| name   | missing_date |
|--------|--------------|
| king   | 2020-08-03   |
| rook   | 2020-08-01   |
| rook   | 2020-08-02   |
| bishop | 2020-08-02   |
| bishop | 2020-08-03   |

Also, Along with it can I get the count of the missing dates for each user if possible?

Answer

You must cross join Table B to the distinct names of Table A and then left join Table A to filter out the matching rows:

select n.name, b.working_date missing_date 
from TableB b
cross join (select distinct name from TableA) n
left join TableA a on a.name = n.name and a.working_date = b.working_date
where a.id is null

If you want to count the missing_dates use the same query and group by name:

select n.name, count(*) missing_dates 
from TableB b
cross join (select distinct name from TableA) n
left join TableA a on a.name = n.name and a.working_date = b.working_date
where a.id is null
group by n.name

See the demo.
Results:

> name   | missing_date
> :----- | :-----------
> king   | 2020-08-03  
> rook   | 2020-08-01  
> rook   | 2020-08-02  
> bishop | 2020-08-02  
> bishop | 2020-08-03 

and:

> name   | missing_dates
> :----- | ------------:
> bishop |             2
> king   |             1
> rook   |             2