不使用 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
LEFT JOIN SQT_Department_Commerce as D ON A.ID = D.CMMD_ID
WHERE S.BUYER_ID = 45 AND D.CMMD_ID IS NULL
