如何将含NOT IN的SQL查询转换为JOIN查询并优化

你可以通过两种常用方式将原查询中的NOT IN替换为与mcsdownloads表的关联操作，避免硬编码ID列表，同时提升查询的可维护性和健壮性：

方法1：LEFT JOIN + IS NULL 排除法 #

这种方式通过左连接mcsdownloads表，筛选出没有匹配记录的商品，等价于原NOT IN的逻辑：

Select
    1 As status,
    e.entity_id,
    e.attribute_set_id,
    e.type_id,
    e.created_at,
    e.updated_at,
    e.sku,
    e.name,
    e.short_description,
    e.image,
    e.small_image,
    e.thumbnail,
    e.url_key,
    e.free,
    e.number_of_downloads,
    e.sentence1,
    e.url_path
From
    catalog_product_flat_1 As e
    Inner Join catalog_category_product_index_store1 As cat_index
        On cat_index.product_id = e.entity_id And
            cat_index.store_id = 1 And
            cat_index.visibility In (3, 2, 4) And
            cat_index.category_id = '2'
    Left Join mcsdownloads As d
        On d.product_id = e.entity_id
Where
    e.free = 1
    And d.product_id Is Null
Order By
    e.number_of_downloads Desc;

逻辑说明：
LEFT JOIN会保留catalog_product_flat_1中所有符合之前关联条件的记录，然后尝试匹配mcsdownloads表的product_id。最后通过d.product_id Is Null筛选出那些在mcsdownloads中没有对应记录的商品，即原查询中需要排除的ID集合。

方法2：NOT EXISTS 子查询 #

如果mcsdownloads表的product_id字段有索引，这种方式通常性能更优，因为数据库会在找到第一个匹配记录后停止搜索：

Select
    1 As status,
    e.entity_id,
    e.attribute_set_id,
    e.type_id,
    e.created_at,
    e.updated_at,
    e.sku,
    e.name,
    e.short_description,
    e.image,
    e.small_image,
    e.thumbnail,
    e.url_key,
    e.free,
    e.number_of_downloads,
    e.sentence1,
    e.url_path
From
    catalog_product_flat_1 As e
    Inner Join catalog_category_product_index_store1 As cat_index
        On cat_index.product_id = e.entity_id And
            cat_index.store_id = 1 And
            cat_index.visibility In (3, 2, 4) And
            cat_index.category_id = '2'
Where
    e.free = 1
    And Not Exists (
        Select 1
        From mcsdownloads As d
        Where d.product_id = e.entity_id
    )
Order By
    e.number_of_downloads Desc;

优势说明：

• 避免了LEFT JOIN可能产生的临时结果集，执行效率更高；
• 相比NOT IN，不会因为mcsdownloads表的product_id存在NULL值导致查询返回空结果（NOT IN遇到NULL时逻辑判定为未知，会过滤所有记录）。

额外建议 #

为了提升查询性能，确保mcsdownloads表的product_id字段创建了单列索引，这样关联或子查询的匹配速度会大幅提升。

内容的提问来源于stack exchange，提问作者Vishal Rathod