MySQL In cannot be rewritten using an internal connection

MySQL In cannot be rewritten using an internal connection

The original interview was carried out as an interview question ..
What MySQL In uses Exists, which is very slow...
The interviewer asked me what to do. The answer was to rewrite it with an inner connection ..
Then the interviewer smiled ..

This script has always been used ..
It wasn't until yesterday that I found out that this script was wrong.

Lab data
Create table song (
Songid int
);
Insert into song values (1), (2), (3 );

Create table song_category_relation (
Category varchar (10 ),
Songid int
);
Insert into song_category_relation values
('China', 1 ),
('China', 2 ),
('China', 3 ),
('Hot topic ', 3 );

Song is a song table
Song_category_relation is a ing table between songs and categories.
A song may have multiple categories.

Requirement.
If you know the ID of a song, find all the categories to which the song belongs, and then find all the songs under all categories.

Use In
Select * from song where songid in (
Select songid from song_category_relation where Category in
(Select Category from song_category_relation where songid = 3)
);

However, if you use an inner connection to rewrite it ..
Select * from song inner join (
Select songid from song_category_relation where Category in
(Select Category from song_category_relation where songid = 3)
) A on (a. songid = song. songid );

We can see that the results of the two methods are not the same ..
Logically, the subquery after In goes through deduplication.
After MySQL 5.6 was implemented through Exists, Semi Join optimization was adopted.

Semi Join reference:

This article permanently updates the link address: