Take MySQL as an example, what is the difference (principle and efficiency) between in and exists and between not in and not exists? What about using indexes?

take MySQL as an example, what is the difference (principle and efficiency) between in and exists and between not in and not exists? What about using indexes? Say that not in and < > will not use indexes, won"t you?

Mysql-Index mysql Database sql

Mar.12,2021

refer to this: https://codeshelper.com/a/11.

< H2 > in terms of efficiency: < / H2 >

1) select * from T1 where exists (select 1 from T2 where T1.a=T2.a)
when the amount of T1 data is small and T2 data is very large, when T1 < T2, 1) the query efficiency is high.
2) select * from T1 where T1.an in (select T2.a from T2)
T1 data is very large and T2 data is small, when T1 > T2, 2) the query efficiency is high.

in short, the general formula is: large appearance, large inner table with IN;, and EXISTS.

" details "

Previous: WebSocket handshake failed 400 error

Next: Jenkins-Blue Ocean problem

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e3baef-4464f.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e3baef-4464f.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?