不使用 not exists 如何改写这条语句？
公司不让使用not   exists，因为效率低，请问是否有其它办法能高效的替代以下这条语句？

select   *   from   (select   T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS   from   SQT_CMMD_SEND_RELATION   as   S  
inner   join   SQT_TradeCommodity   as   T   on   S.CMMD_ID   =   T.ID   where   S.BUYER_ID   =   45   )   as   A  
where   not   exists   (   select   1   from   SQT_Department_Commerce   as   D   where   A.ID   =   D.CMMD_ID     )



------解决方案--------------------
公司不让使用not exists， 因为效率低???
------解决方案--------------------
--用join
select *
from (
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION S
join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45
) A
join SQT_Department_Commerce D on A.ID <> D.CMMD_ID

------解决方案--------------------
select * from (select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45 ) as A
where int ( select 1 from SQT_Department_Commerce as D where A.ID <> D.CMMD_ID )
------解决方案--------------------
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
Left join SQT_Department_Commerce as D ON A.ID = D.CMMD_ID
Where S.BUYER_ID = 45 AND D.CMMD_ID is NUll


------解决方案--------------------
用left join，不存在的时候 D.CMMD_ID is null，试试看

select * from (
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S inner join SQT_TradeCommodity as T
on S.CMMD_ID = T.ID where S.BUYER_ID = 45
) as A
left join SQT_Department_Commerce as D
on A.id = D.CMMD_ID
where D.CMMD_ID is null

------解决方案--------------------
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID where S.BUYER_ID = 45
left join SQT_Department_Commerce as D on S.CMMD_ID=D.CMMD_ID
where D.CMMD_ID is null

------解决方案--------------------
----如果SQT_Department_Commerce的CMMD_ID是唯一的
select T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,
T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID
LEFT JOIN SQT_Department_Commerce as D ON A.ID = D.CMMD_ID
WHERE S.BUYER_ID = 45 AND D.CMMD_ID IS NULL

----否则加DISTINCT
select DISTINCT T.ID,T.COMMERCE_NAME,T.BRAND,T.MANU_NAME,T.SPEC,T.MODEL,T.DEFAULT_MEASURE,
T.PRICE,T.SALER_NAME,T.STATE,T.HASFITTINGS
from SQT_CMMD_SEND_RELATION as S
inner join SQT_TradeCommodity as T on S.CMMD_ID = T.ID