CODEWITHSUNDEEP
mysql
vikas.badola
vikas.badola

Reputation: 137

How to replace IN clause with JOIN in sql?

I have following query .

select entity_id ,parent_id,name as label , url_key as name from magento_catalog_category_flat_store_1 where entity_id IN(
SELECT distinct T1.parent_id
FROM magento_catalog_category_flat_store_1 T1 
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON 
           T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on 
           T2.entity_id = T3.activity_id)

The above query decreases the performance because of IN clause . Can anyone tell me how to replace IN clause with JOIN in this query ?

Thanks.

Upvotes: 3

Views: 11886

Answers (3)

CompanyDroneFromSector7G
CompanyDroneFromSector7G

Reputation: 4527

Not tested, obviously!

WITH (
SELECT distinct T1.parent_id
FROM magento_catalog_category_flat_store_1 T1 
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON 
           T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on 
           T2.entity_id = T3.activity_id)
) T0
select entity_id ,parent_id,name as label , url_key as name from 
magento_catalog_category_flat_store_1 T1 
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON 
           T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on 
           T2.entity_id = T3.activity_id
INNER JOIN T0
ON T0.entity_id = magento_catalog_category_flat_store_1.entity_id

Upvotes: 1

Barmar
Barmar

Reputation: 782785

In general

SELECT ...
FROM Table1
WHERE col1 IN (SELECT col2 FROM ...)

can be converted to:

SELECT ...
FROM Table1 t1
JOIN (SELECT DISTINCT col2 FROM ...) t2
ON t1.col1 = t2.col2

Upvotes: 18

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try like this

SELECT Tab1.entity_id ,Tab1.parent_id,Tab1.name As Label , Tab1.url_key As Name 
FROM magento_catalog_category_flat_store_1 Tab1 JOIN
(
SELECT distinct T1.parent_id
FROM magento_catalog_category_flat_store_1 T1 
INNER JOIN magento_catalog_category_flat_store_1 as T2 ON 
           T1.entity_id = T2.parent_id
RIGHT JOIN sohyper_region_activity as T3 on 
           T2.entity_id = T3.activity_id
) Tab2 ON Tab1.entity_id = Tab2.parent_id

Upvotes: 0

Related Questions