SQL statement optimization-replace IN with EXISTS and use NOTEXISTS to replace NOTIN statements-mysql tutorial

SQL statement optimization: Replace IN with EXISTS, and replace NOTIN with NOTEXISTS

SQL statement optimization: use EXISTS to replace IN and NOT EXISTS to replace NOT IN statements

In many basic table-based queries, to meet one condition, you often need to join another table. In this case, using EXISTS (or not exists) usually improves the query efficiency. IN a subquery, the not in clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). To avoid using not in, we can rewrite it into an Outer join (Outer Joins) or not exists.

For example

I want to query the redundant data in the Sendorder table (no data connected to reg_person or worksite)

SQL = "select Sendorder. id, Sendorder. reads, Sendorder. addtime from Sendorder where Sendorder. person_id not in (select user_id from reg_person) or Sendorder. worksite_id not in (select id from worksite) order by Sendorder. addtime desc"
Program Execution time: 40109.38 MS

SQL = "select Sendorder. id, Sendorder. reads, Sendorder. addtime from Sendorder where not EXISTS (SELECT id FROM reg_person where reg_person.user_id = Sendorder. person_id) or not EXISTS (SELECT id FROM worksite where worksite. id = Sendorder. worksite_id) order by Sendorder. addtime desc"
Program Execution time: 8531.25 MS

Obviously, using not EXISTS is much more efficient.