When the dataset of Table B must be smaller than the dataset of Table A, in is better than exists. when the dataset of Table A is smaller than that of Table B, exists is used to better than in optimization principle: small tables drive large tables, that is, small datasets drive large datasets.
############ Principle (RBO )#####################
Select * from A where id in (select id from B) is equivalent to: for select id from Bfor select * from A where A. id = B. id
When the dataset of Table B must be smaller than the dataset of Table A, in is better than exists.
Select * from A where exists (select 1 from B where B. id = A. id) is equivalent to for select * from Afor select * from B where B. id = A. id
When the dataset of Table A is less than the dataset of Table B, exists is better than in.
Note: the ID fields of Table A and Table B should be indexed.
For example:
/** Execution time: 0.313 s **/SELECT SQL _NO_CACHE * FROM rocky_member m WHERE EXISTS (SELECT 1 FROM rocky_vip_appro a WHERE m. ID =. user_id AND. passed = 1);/** Execution time: 0.160 s **/SELECT SQL _NO_CACHE * FROM rocky_member m WHERE m. ID in (select id from rocky_vip_appro WHERE passed = 1 );
The usage of not in and not exists is similar.