Mysql: полезный трюк с count() и count(distinct)

Хочу поделиться одним интересным решением, к которому мне удалось прийти сегодня во время оптимизации запроса поиска пользователей. В выборке необходимо было возвращать количество общих групп текущего пользователя и меня — того, кто производит поиск. Что называется total shared groups. В итоге всё поместилось в один компактный запрос без подзапросов (что очень критично, позже объясню почему) с использованием одной таблицы в FROM, без GROUP BY и HAVING.

Представим задачу более наглядно. Примем следующие таблицы:
— group (group_id)
— user (user_id)
— user_group (хранит связки user_id-group_id)

Необходимо узнать количество групп, в котором состоят пользователи user1 и user2. То есть найти пересечения между юзерами по таблице user_group.

1. Следующий запрос выдаст количество всех записей из таблицы user_group, которые принадлежат юзерам user1 и user2.
SELECT
COUNT(`group_id`)
FROM
`user_group`
WHERE
`user_id` IN (:user1,:user2)


2. А вот этот запрос выдаст количество записей из таблицы user_group, которые принадлежат юзерам user1 и user2, но с уникальным group_id:
SELECT
COUNT(DISTINCT `group_id`)
FROM
`user_group`
WHERE
`user_id` IN (:user1,:user2)


В данном случае, если нашлись такие группы, которые были привязаны сразу к двум нашим пользователям, то они будут «схлопнуты» в одну строку.

3. В итоге приходим к виду:
SELECT
COUNT(`group_id`) - COUNT(DISTINCT `group_id`)
FROM
`user_group`
WHERE
`user_id` IN (:user1,:user2)


Если отнять общее число групп двух пользователей и число групп, с учётом уникальности group_id, то в рузультате получим наше искомое total shared groups — сколько общих групп у двух пользователей.

Как ещё можно было решить задачу?
SELECT
COUNT(*)
FROM
(
SELECT
`group_id`
FROM
`user_group`
WHERE
`user_id` IN (:user1,:user2)
GROUP BY
`group_id`
HAVING
COUNT(*) > 1
) as `shared_groups`


Такой подход я встречал довольно часто. Но у него есть один существенный минус — использование подзапроса. Если :user1 или :user2 не передаются в запрос, а подставляются динамически из запроса верхнего уровня (например, какой-нибудь `user1`.`user_id`), то данный вариант выдаст ошибку.