How to use not in to optimize MySql

Recently, when using select query in a project, I used not in to exclude the unused primary key ID. The SQL used at the beginning was as follows:

select 
  s.SORT_ID,
  s.SORT_NAME,
  s.SORT_STATUS,
  s.SORT_LOGO_URL,
  s.SORT_LOGO_URL_LIGHT
from SYS_SORT_PROMOTE s
  WHERE
    s.SORT_NAME = 'Must-Listen Classics'
    AND s.SORT_ID NOT IN ("SORTID001")
  limit 1;

When there is a lot of data in the table, the execution time of this SQL is long and the execution efficiency is low. I found information online that it can be optimized using left join. The optimized SQL is as follows:

select 
  s.SORT_ID,
  s.SORT_NAME,
  s.SORT_STATUS,
  s.SORT_LOGO_URL,
  s.SORT_LOGO_URL_LIGHT
from SYS_SORT_PROMOTE s
left join (select SORT_ID from SYS_SORT_PROMOTE where SORT_ID=#{sortId}) b
on s.SORT_ID = b.SORT_ID
  WHERE
    b.SORT_ID IS NULL
    AND s.SORT_NAME = 'Must-Listen Classics'
  limit 1;

In the above SORT_ID=#{sortId}, sortId passes in the ID value that needs to be excluded in the SORT_ID field. When performing a left outer join, the field to be filtered (SORT_ID) is used as the join condition. Finally, add b.SORT_ID IS NULL to the where condition to filter out the related data in the table.

Write down the essay here to record the optimization process.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.