SQLite3 Query COUNT combining two tables

Question

I'm having trouble trying to get my query working as intended, I have two tables (names and classes). Where names has two rows (username and name) & classes has three rows (class, username and time).

I'm trying to extract username, name and the amount of classes each person teaches (see table below), which can be figured out by looking at how many times a given "username" appears in the "classes" table. The problem is that there are some usernames that appear in the "names" table but don't appear in the "classes" table, these users should be assigned "0" in the count. There's also users who appear in the classes table but don't appear in the names table, these people should still be taken into account but with a null value as their name.

names table                  classes table
 __________________          ________________________
|username | name  |         |class | username |time |
|ab       | Ali   |         |xxx   | ch       |xxx  |
|ch       | Chi   |         |xxx   | dd       |xxx  |
|dd       | Dia   |         |xxx   | ee       |xxx  |

etc...

desired output from query
 __________________________________________
|username | name  |number of classes taught|
|ab       | Ali   |0                       |
|ch       | Chi   |1                       |
|dd       | Dia   |3                       |
|ee       | null  |5                       |

etc....

I have the following query so far

SELECT
    names.username,
    name,
    COUNT(*)
FROM
    classes
INNER JOIN names ON
    names.username = classes.username
GROUP BY
    classes.username
ORDER BY
    COUNT(*)

actual output from query
 __________________________________________
|username | name  |number of classes taught|
|ch       | Chi   |1                       |
|dd       | Dia   |3                       |

But this gives me an output that doesn't include users who don't teach and also excludes users who don't have a real name. I'm not sure how to resolve this issue, would I need to combine the two username rows without creating duplicates? or something along those lines?

Answer 1

If you want all users, start with the user table. If you want all users that don't have any classes, do a LEFT join and not an INNER join

SELECT
    names.username,
    name,
    COUNT(*)
FROM
    names 
LEFT JOIN classes ON
    names.username = classes.username
GROUP BY
    classes.username
ORDER BY
    COUNT(*)

Answer 2

What you need is a FULL OUTER join which is not supported by SQLite, but can be emulated with LEFT joins and UNION ALL and then aggregate:

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

See a simplified demo.