How to optimize SQL query using JOIN for same nested SELECT?

Question

MySQL

Help optimize query via JOIN for nested subqueries joined by UNION. I remember that this should be more logical and faster

SELECT 
  product_id, 
  min_price, 
  max_price, 
  82.23546 as rate 
FROM 
  wp_wc_product_meta_lookup 
WHERE 
  product_id IN (
    SELECT 
      post_id 
    FROM 
      wp_postmeta 
    WHERE 
      meta_key = 'convert_to_rub' 
      AND meta_value = 1
  ) 
UNION 
SELECT 
  product_id, 
  min_price, 
  max_price, 
  1 as rate 
FROM 
  wp_wc_product_meta_lookup 
WHERE 
  product_id NOT IN (
    SELECT 
      post_id 
    FROM 
      wp_postmeta 
    WHERE 
      meta_key = 'convert_to_rub' 
      AND meta_value = 1
  ) 
GROUP BY 
  product_id

thanks in advance

Answer 1

SELECT 
  product_id, 
  min_price, 
  max_price, 
  CASE WHEN ISNULL( t2.post_id) THEN 1 ELSE 82.23546 END as rate
FROM 
  wp_wc_product_meta_lookup as t1
LEFT JOIN wp_postmeta as t2 ON 
  t1.product_id = t2.post_id AND 
  t2.meta_key = 'convert_to_rub'  AND 
  t2.meta_value = 1