By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table names(username text, name text);
✓
insert into names(username, name) values
('ab', 'Ali'), ('ch', 'Chi'), ('dd', 'Dia');
✓
create table classes(class text, username text);
✓
insert into classes(class, username) values
('xxx', 'ch'), ('xxx', 'dd'), ('xxx', 'oo'), ('xxx', 'dd'), ('xxx', 'pp'), ('xxx', 'pp');
✓
select username, name, count(*) [number of classes taught]
from (
select n.username, n.name, c.class
from names n left join classes c
on c.username = n.username
union all
select c.username, n.name, c.class
from classes c left join names n
on c.username = n.username
where n.username is null
)
group by username, name
| username | name | number of classes taught |
|---|---|---|
| ab | Ali | 1 |
| ch | Chi | 1 |
| dd | Dia | 2 |
| oo | null | 1 |
| pp | null | 2 |