我们先来比对一下这两句sql:
一、
SELECT * FROM
(SELECT d.* ,g.goods_score AS gid FROM mall_goods g
RIGHT JOIN (select a.*, b.name _state FROM mall_goods_info a, code b where 1=1 and b.type = '商品状态' and a.store_id = '317' and a.del_flag = 1 and a.state = b.code ) d
ON g.id =d.id ) e
WHERE ISNULL(e.gid)
二、
select a.*, b.name _state from mall_goods_info a
inner join code b
on b.type = '商品状态' and a.state = b.code
where a.store_id = '317' and a.del_flag = 1 and a.id not in (select mall_goods.id from mall_goods)
查询结果一样,主要的问题就是查询某张表的某个字段的值不存在于另一张表的结果集。
一 的方法是先righ join等于的,因为是右连接,所以不等于的mall_goods表就是null,最后where isnull
二的方法是直接not in,这个值不存在另一张表
查询时间对比(对比了几次):二的结果快于一的。