MySQL 联表查询 not in 优化方法 left join

参考博客

MYSQL之not in优化方法：left join

example

-- ----------------------------
-- 查询 a 表 符合条件的空闲商品 
-- ----------------------------
select
	* 
from
	sys_orders a
where
	a.orders_nums > a.success_counts 
	and a.start_nums + a.orders_nums > a.now_nums 
	and a.orders_state <= 1 
	and a.lock_state = 0 
	and a.orders_type = 0 
	and a.goods_type = 0;
-- ----------------------------
-- 查询 b 表 符合条件领取过得商品
-- ----------------------------
select
	* 
from
	sys_orders_log b 
where
	b.note_id = 1 
	and b.orders_type = 0 
	and b.request_id = 1234;
	
-- ----------------------------
-- 合并 b表查询出来的数据 a表同时存在时过滤掉这些数据
-- ----------------------------

-- ----------------------------
-- not in
-- ----------------------------
select
	a.* 
from
	sys_orders a 
where
	a.orders_nums > a.success_counts 
	and a.start_nums + a.orders_nums > a.now_nums 
	and a.orders_state <= 1 
	and a.lock_state = 0 
	and a.orders_type = 0 
	and a.goods_type = 0 
	and a.note_id not in ( select note_id from sys_orders_log b where b.orders_type = a.orders_type and b.request_id = 1234 ) 
order by
	rand() 
	limit 1;
-- ----------------------------
-- left join
-- ----------------------------
select
	a.* 
from
	sys_orders as a
	left join ( select * from sys_orders_log where orders_type = 0 and request_id = 1234 ) as b on a.note_id = b.note_id 
where
	a.orders_nums > a.success_counts 
	and a.start_nums + a.orders_nums > a.now_nums 
	and a.orders_state <= 1 
	and a.lock_state = 0 
	and a.orders_type = 0 
	and a.goods_type = 0 
	and b.note_id is null 
order by
	rand() 
	limit 1;