MySql self JOIN query

Question

I have this table

mysql> describe skill_usage;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| skill_id | int(11) | NO   | MUL | NULL    |       |
| job_id   | int(11) | NO   | MUL | NULL    |       |
+----------+---------+------+-----+---------+-------+

and know that in my data, there is a single job_id (6) which was used for both skill_id 3 and 4:

mysql>  select * from skill_usage;
+----------+--------+
| skill_id | job_id |
+----------+--------+
|        1 |      1 |
|        2 |      2 |
|        3 |      3 |     <----  matches only one part of the AND clause
|        3 |      4 |     <----  matches only one part of the AND clause
|        2 |      5 |
|        3 |      6 |     <==== matches both parts of the AND clause
|        4 |      6 |     <====
|        2 |      7 |
+----------+--------+
8 rows in set (0.00 sec)

Here's what I tried:

SELECT DISTINCT s1.job_id FROM skill_usage AS s1 
  INNER JOIN skill_usage AS s2 ON s1.job_id = s2.job_id
    WHERE s1.skill_id IN (3,4)
    AND   s2.skill_id IN (3,4)

which I thought meant "find all job_id which matches both skill_id 3 and skill_id 4".

Apparently not:

mysql> SELECT DISTINCT s1.job_id FROM skill_usage AS s1
    ->   INNER JOIN skill_usage AS s2 ON s1.job_id = s2.job_id
    ->     WHERE s1.skill_id IN (3,4)
    ->     AND   s2.skill_id IN (3,4);
+--------+
| job_id |
+--------+
|      3 |
|      4 |
|      6 |
+--------+
3 rows in set (0.00 sec)

What am I doing wrongly? How should my query read? I think that it's time for a good book or Udemy course, but none that I own cover self join.

My query is correctly finding job_id = 6, but wrongly (IMO), finding job_id 3 and 4. I would expect them to fail the AND clause.

Answer 1

You may use aggregation here:

SELECT job_id
FROM skill_usage
WHERE skill_id IN (3, 4)
GROUP BY job_id
HAVING MIN(skill_id) <> MAX(skill_id);

This query should benefit from the following index:

CREATE INDEX idx ON skill_usage (skill_id, job_id);

Both the WHERE and HAVING clauses, as written, are sargable, and should be able to take advantage of this index.