not in 语句效率比not exists 效率要低.在对sql执行效率要求比较高的情况下,需要把not in 语句转化为not exists.
有数据表如下图:
现在需要找出在ParentMissionID列中存在但在MissionID列中不存在的数字.
用not in 语句很容易实现:
- select p.[ParentMissionID] from
- (select [ParentMissionID] FROM [Test].[dbo].[TestMissionParent] where projectId=1) as p
- where p.[ParentMissionID] not in (select [MissionID] FROM [Test].[dbo].[TestMissionParent] where projectId=1)
用not exists 语句则为:
- SELECT [ParentMissionID]
- FROM (SELECT [ParentMissionID]
- FROM [Test].[dbo].[TestMissionParent]
- where projectID=1) as p where not exists
- (select 1 from (select missionId from [Test].[dbo].[TestMissionParent] where projectId=1) as m where m.missionId=p.parentMissionId)
其他情况可以根据这个规律自行改造.