求一SQL优化解决思路

求一SQL优化

SQL code


  SELECT 
     mp.id,
     mp.phone,
     mp.carrier_id,
     mp.region_id,
     rg.name AS regionname,
     ca.name AS carriername,
     rg.area_identity AS areaidentity 
FROM
     gsms_region_carrier_map AS mp 
     JOIN gsms_carrier AS ca 
          ON mp.carrier_id = ca.id 
     JOIN gsms_region_code AS rg 
          ON mp.region_id = rg.id 
WHERE mp.is_removed = FALSE 
     AND mp.id NOT IN 
     (SELECT 
          mp2.remove_id 
     FROM
          gsms_region_carrier_map AS mp2 
     WHERE is_removed = TRUE)

我想把后面的那个子查询变成 join 形式的.

ps:gsms_region_carrier_map 表id是主键.

SELECT

mp.id,

mp.phone,

mp.carrier_id,

mp.region_id,

rg.name AS regionname,

ca.name AS carriername,

rg.area_identity AS areaidentity

FROM

gsms_region_carrier_map AS mp

JOIN gsms_carrier AS ca

ON mp.carrier_id = ca.id

JOIN gsms_region_code AS rg

ON mp.region_id = rg.id

left join gsms_region_carrier_map mp2

on mp.id =mp2.remove_id

WHERE mp.is_removed = FALSE

AND mp2.remove_id is null

相关文章：

你感兴趣的文章：

标签云：