trying to learn MySql and would like a further explanation on a query-CodePudding

okay I know that the query works as it runs just fine in my DB that I use for practice. however, I am still extremely new to MySQL and would just like to understand a little bit more.

here is my query...

SELECT
    photos.id, photos.image_url, COUNT(*) as total
FROM photos
JOIN likes 
    ON likes.photo_id = photos.id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1;

so my question is, how does the aggregate function "COUNT(*)" know to count from the correct table? I want it to count from the "likes" table and it does that, but how does it understand that is what I am asking?

I was originally thinking I would need to do a "COUNT(likes.photo_id)" but it was unnecessary.

so how does it know? am I just going down a rabbit hole that in the long run just does not matter?

CodePudding user response：

Count() counts the number of rows returned by the query as a whole. If you run the query without the count, it returns a specific number of rows. That's what Count() is counting.

CodePudding user response：

It isn't counting rows in either photos or likes. It's counting rows in the joined result set.

Here's a cool thing about SQL: the result of relational operations (for example JOIN or UNION) between tables is... another table! It isn't a table that is stored in your database, but it's a table.

You can think of an analogy in arithmetic: the sum of two positive integers is another positive integer. In mathematics, this is called a closure.

It's the same in relational algebra. When you combine two tables with one of the relational operators, the result is another thing that could be a table itself.

So COUNT(*) is not counting rows in either table. It's counting the rows in the table produced as the result of the JOIN.