Getting all rows with common value in column A and specific value in column B

In MySQL I have table matcher_table with columns matcher and type. Records which are put here can have many different values for type column. One type matches another one only when value for matcher column is the same.
Let’s say I have to find all matching records for 5 types. Which would be the best approach/query in order to achieve this?

---

The table would be:

CREATE TABLE `matcher_table` (
  `id` INT NOT NULL,
  `matcher` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

Now let’s say we have this values in the table:

id | matcher | type
1  | match1  | type1
2  | match1  | type2
3  | match1  | type3
4  | match2  | type4
5  | match2  | type2
6  | match3  | type1
7  | match3  | type2
8  | match3  | type3

If I need to get matching data for types (type1, type2, type3), than I must get rows with ID 1, 2, 3, 6, 7, 8 (due to match1 and match3).
If I need to get matching data for types (type1, type2, type3, type4) than I must get no records fulfilling this match.

Answer

I would recommend three exists clauses — because you want the original rows:

select mt.*
from matcher_table mt
where exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type1'
             ) and
      exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type2'
             ) and
      exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type3'
             );

The advantage of this approach is that it avoids aggregation and it can make use of an index on matcher_table(matcher, type). I would expect this to have very good performance in comparison to other approaches.