大多数情况下,程序员比较喜欢使用in来查询符合某些条件的数据,最近在查询某个角色有哪些用户的方法中,使用了in语句:
SELECT COUNT(1) FROM baseuser WHERE BaseUser.DeletionStateCode = 0 AND BaseUser.Enabled = 1 AND BaseUser.IsVisible = 1 AND BaseUser.Id > 0 AND BaseUser.Id IN (SELECT UserId FROM spysxtUserRole WHERE RoleId = '6d989d918dfe428c95e4469f866b1299' AND Enabled = 1 AND DeletionStateCode = 0)
可以看到时间是0.249ms;
一位朋友看到后,建议改为Join,改后的语句:
select count(1) from baseuser A left join (SELECT UserId FROM spysxtUserRole WHERE RoleId = '6d989d918dfe428c95e4469f866b1299' AND Enabled = 1 AND DeletionStateCode = 0) B on A.Id = B.UserId where B.UserId is not NULL and A.DeletionStateCode = 0 AND A.Enabled = 1 AND A.IsVisible = 1 AND A.Id > 0
耗时0.125ms,是用in查询的一半;
查询出的结果都是一样的。
可以看出,使用join要比用in写的复杂一些,但是效率确实提高跟多。建议能使用join的尽量改一下。