left join keep all from first table count null as zero see results with count zero

WRONG:

SELECT d.tid AS tid, d.name AS name, d.vid AS vid, COUNT(x.nid) AS count FROM taxonomy_term_field_data d LEFT OUTER JOIN taxonomy_index x ON d.tid=x.tid GROUP BY x.tid;

Has to be grouped by the first table’s tid, so d.tid, or (surprise!) it cannot count what is not here.

CORRECT:

SELECT d.tid AS tid, d.name AS name, d.vid AS vid, COUNT(x.nid) AS count FROM taxonomy_term_field_data d LEFT OUTER JOIN taxonomy_index x ON d.tid=x.tid GROUP BY d.tid;

And that same query, the correct way around, in modern Drupal:

    $terms_query = $this->database->select('taxonomy_term_field_data', 'd')
      ->fields('d', ['tid', 'name', 'vid']);
    $terms_query->leftJoin('taxonomy_index', 'x', 'd.tid = x.tid');
    $terms_query->addExpression('COUNT(x.nid)', 'count');
    $terms_query->groupBy('d.tid');
    if ($vocabulary) {
      $terms_query->where('vid = :vid', [':vid' => $vocabulary->id()]);
      if (isset($depth)) {
        $terms_query->having('count <= :depth', [':depth' => $depth]);
      }
    }
    return $terms_query->execute()->fetchAll(PDO::FETCH_ASSOC);

I was trying to throw in a CASE WHEN x.nid IS NULL THEN 0 ELSE x.nid expression in there but, aside from making the whole SQL statement invalid the way i was doing it, it is not necessary. SQL COUNT() will count the NULLs as zero, just need to make certain the GROUP BY clause is on a field that will always be there.