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"
ProgramExecution 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.