Why doesn't mysql query output a record where the number of matches is 0?

Question

MySQL

Hello! There is a query that should display the services and the number of subscriptions to these services within a certain number.
This line WHERE service.id in (13,74,71,72)contains a list of services that need to be checked, and I know for sure that the subscriber is not subscribed to the service 74

Here is my request

SELECT service.id as service_id, count(subs.id) as c
FROM Subscriptions subs
INNER JOIN Services service ON subs.service_id = service.id
WHERE service.id in (13,74,71,72)
  AND subs.msisdn=992777757031
GROUP BY service.id
ORDER BY c DESC;

It outputs the following:



And as you can see from the result of the service with the number 74 and its quantity, there is no, why? Why is the request not working properly? What needs to be fixed?

Answer 1

The list of required ids must be the source of the request data, and the base one. And in the event of the possible absence of related records, the binding should be external.

SELECT service.id AS service_id, COUNT(subs.id) AS c
FROM ( SELECT 13 id UNION 
       SELECT 74    UNION
       SELECT 71    UNION
       SELECT 72 ) AS service
LEFT JOIN Subscriptions subs ON subs.service_id = service.id AND subs.msisdn=992777757031
GROUP BY service.id
ORDER BY c DESC;