Warm tip: This article is reproduced from stackoverflow.com, please click
aggregate-functions group-by mysql sql

SQL join returns null records when there are no data in database

发布于 2020-04-03 23:38:40

I am trying to get forum information and count of topics and posts. I am using mysql

SELECT forums.id, forums.url, forums.title, forums.description, 
       forums.icon, COUNT(topics.id) topics, COUNT(posts.id) posts 
FROM forums JOIN 
     topics 
     ON forums.id = topics.to_forum JOIN 
     posts 
     ON topics.id = posts.to_topic 

But when there is no forum, no topic, no posts it returns enter image description here

I´ve tried to use left join, right join, inner join (as I read on internet) but nothing seems to work. The left join works only if there is forum already created than it fills the data. I need it to return no record if there is not forum created no this NULL nonsence. Here are structures of the tables, there are no data in database so I dont get why it returns this

Forums enter image description here

Topics enter image description here

Posts enter image description here

Questioner
DevByGames
Viewed
33
44.5k 2020-01-31 20:37

Try appending GROUP BY forums.id to your query.

You're mixing aggregate (COUNT()) operations with ordinary column operations in the same SELECT. If you give any aggregate operations and you don't give GROUP BY, you'll always get exactly one row back from the SELECT. In standard SQL your query will fail. MySql in nonstandard: it tries to guess what you want in the non -aggregated columns. If there are no rows to count it guesses NULL.

Read this: dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

In other words, as soon as you use COUNT() in a query you need to understand GROUP BY even if you don't put it in your query.