How to select "AND" and "OR" in MySQL?

Question

MySQL

Good afternoon,
The question must be extremely simple, but I suddenly realized that I did not know the answer to it at all.
How to competently make a selection by tags using only MySQL (preferably in one query and without stored procedures)?
There are three tables: a users table containing users, a tags table containing id and tag names, and a user_tags table containing (user_id, tag_id) pairs. Task: to implement a selection of all users who have each of the set of tags (1, 2, ..., N), and also (optionally) have at least one of these tags.
And if in the second case, probably, DISTINCT can help (although I had some problems with it recently), then I have no idea about the first one.
Suggest ideas please.

Answer 1

Depends on whether the pair (user_id, tag_id) is unique or not.
For the unique, everything is simple

SELECT `u`.*
  FROM `users` AS `u`
  JOIN `user_tags` AS `ut` ON `ut`.`user_id` = `u`.`id`
    AND `ut`.`tag_id` IN (1, 2, ..., N)
  GROUP BY `u`.`id`
  HAVING COUNT(*) = N

For non-unique DISTINCT is added

SELECT `u`.*
  FROM `users` AS `u`
  JOIN `user_tags` AS `ut` ON `ut`.`user_id` = `u`.`id`
    AND `ut`.`tag_id` IN (1, 2, ..., N)
  GROUP BY `u`.`id`
  HAVING COUNT(DISTINCT `ut`.`tag_id`) = N

Answer 2

Good. I will not give you a ready-made result, but I will give you food for thought and one example. No Distinct is needed, it has to be used in rare cases. Otherwise, he says that either the database is designed incorrectly, or the request is written incorrectly.
select * from tbl_user where id_user in (select id_user from tbl_user_tag);
One line - and here you have all the users who have at least one attribute.
No less simple is a selection of all users who have all the signs, but this is for independent reflection. :) Good luck.