SQL count() not showing values of 0-CodePudding

i just started my journey with SQL, and made some tables of Cyclists, and Cycling Teams.

Cyclist's table contains columns: ID, Name, Team (which is foreign key of TEAMS ID) Team's table contains columns: ID, Name, Number of Cyclists

I want to Count number of Cyclists in each team, by using count() function ( Or basically any function, i just want to make it work )

After many minutes i figured out this query:

SELECT teams.name,
count(*) AS NumberOfCyclists FROM cyclists
JOIN teams ON cyclists.team = teams.id
group by teams.name;

and i Achieved this:

Which is all good, but when i LEFT JOIN i achieve:

My question is: How to get all of the teams (there are 15 of them, not 11), even those where the count of the cyclists is 0?

CodePudding user response：

I think you misunderstand how LEFT JOIN works. The order of tables in the join is important. In a LEFT JOIN, the query returns all the rows in the left table, even if there are no matching rows in the right table. In your query, the left table is cyclists, and the right table is teams.

So your query is currently returning all cyclists, including those who have no team (the result shows that there are 3 cyclists who have no team). This is the reverse of what you want, which is all teams, even those with no cyclists.

If you want to return all the teams, then either reverse the tables in your join:

...
FROM teams
LEFT OUTER JOIN cyclists ON cyclists.team = teams.id
...

Or you could achieve the same result by using RIGHT join.

...
FROM cyclists
RIGHT OUTER JOIN teams ON cyclists.team = teams.id
...

CodePudding user response：

You must count not the amount of rows (COUNT(*)) which cannot be zero but the amount of non-NULL values in definite column (the column which is used in joining condition usage is recommended) taken from right table (COUNT(table.column)). With LEFT JOIN, of course.

But the logic needs teams table to be left. And finally:

SELECT teams.name,
       count(cyclists.team) AS NumberOfCyclists 
FROM teams 
LEFT JOIN cyclists ON cyclists.team = teams.id
group by teams.name;

CodePudding user response：

Try this:

SELECT teams.name,
count(cyclists.id) AS NumberOfCyclists
FROM teams
LEFT JOIN cyclists ON cyclists.team = teams.id
group by teams.name;

The reason why this works instead of the way you have it is because it selects Teams as the base table to draw results from instead of Cyclists.

If there isn't a Cyclist record corresponding to a Team, then the Team is essentially null, and they are grouped together as such (with a null name). By going from Teams into Cyclists, you are saying to take each Team and find the Cyclist records corresponding to the Team, in which case there could be 0 or more.

CodePudding user response：

As you LEFT JOIN, you get all rows from the table cyclists which can have a partner teams, when not all teams rows are NULL

So you have rows that have no oartner