How to convert a NOT IN query into a JOIN query

I have this following query that needs to be optimized

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.entity_id Not In (13863, 14096, 13856, 13924, 15875, 15869, 13788, 15977, 15873, 17141, 22214, 16900, 14485,
    15628, 15656, 14220, 14259, 14284, 13875, 13216, 14168, 13892, 16540, 19389, 17286, 16591, 30178, 31517, 31734,
    31621, 2487, 2486, 2485, 2484, 2483, 2482, 2481, 2480, 2479, 2478, 2477, 2475, 2474, 2473, 13402, 13427, 13694,
    13774, 13804, 13837, 13849, 13864, 30299, 30300) And
    e.free = 1
Order By
    e.number_of_downloads Desc;

Here The ids passed in NOT IN() are the "product_id" column values from a table named "mcsdownloads"

So my goal here is to replace NOT IN with a JOIN operation on table "mcsdownloads".

please help !

Solution

You are looking for LEFT JOIN ... WHERE ... IS NULL:

SELECT  ...
    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
    LEFT JOIN  mcsdownloads AS m  ON m.entity_id = e.entity_id
    WHERE  cat_index.store_id = 1
      And  cat_index.visibility In (3, 2, 4)
      And  cat_index.category_id = '2'
      AND  e.free = 1
      AND  m.entity_id IS NULL

(Note: I moved the filtering criteria from ON to WHERE. ON is used for saying how tables relate; WHERE is for filtering.)

Indexes needed:

cat_index:  INDEX(store_id, category_id, visibility, product_id)
e:  INDEX(free, number_of_downloads)
m:  INDEX(entity_id)