This returns all data from the join condition columns of the left table and all matching data in the right table. All other rows in the right table that do not match the join condition will return null values in the condition column. The following query will return all the repository names from the sample_repos table (LEFT-hand-side table of the join) and also the count of commits for each of those repositories from the sample_commits table (RIGHT-hand-side table of the join). If the sample_commits table does not have any commits for the repository, then 0 is returned:

#legacySQL
SELECT repos.repo_name, COUNT(repocommits.commit) as commit_count
FROM [bigquery-public-data:github_repos.sample_repos] repos
LEFT JOIN [bigquery-public-data:github_repos.sample_commits] repocommits
ON repos.repo_name = repocommits.repo_name
GROUP BY repos.repo_name
order by commit_count