SQL:除非与否 [英] SQL: Except vs Not In
问题描述
我们有多种方法可以过滤SQL Server中结果集中的行。我想知道哪个更好的性能或具有特定的用例,在哪里使用这个功能?
I we do have multiple way to filter the rows from result set in SQL Server. I would like to know which is better in terms of performance or have specific use cases where to use this each functions ?
在我的用例我想要 排除其他表格中存在的某些行。
In my use case I want to exclude the some rows which does exists on other table.
SELECT        
[AGENT_URN],    
[QUEUE_URN],  
[ACTIVITY_ID]
$
FROM#ChatActivityStateDetail
在@pDate_From和 之间的DATE_TIME_UTC @ pDate_To   
且ACTIVITY_ID不在(从#ConferenceChatDetail中选择ACTIVITY_ID)
SELECT
[AGENT_URN],
[QUEUE_URN],
[ACTIVITY_ID]
FROM #ChatActivityStateDetail
WHERE DATE_TIME_UTC between @pDate_From and @pDate_To
and ACTIVITY_ID not in (select ACTIVITY_ID from #ConferenceChatDetail)
我想如果除了我们需要在select中有精确的列数来自两个表,而我们在没有的情况下具有灵活性。
因为NOT IN是一种子查询,所以我有点担心性能。分享您对
的替代解决方案的看法 实现 this没有性能影响。
推荐答案
以下会有更好的表现:
SELECT        
[AGENT_URN],    
[QUEUE_URN],  
[ACTIVITY_ID]
$
FROM#ChatActivityStateDetail
在@pDate_From和 之间的DATE_TIME_UTC @ pDate_To   
且不存在(从#ConferenceChatDetail中选择1,其中a.ACTIVITY_ID =#ChatActivityStateDetail.ACTIVITY_ID)
- 和ACTIVITY_ID不在(从中选择ACTIVITY_ID) #ConferenceChatDetail)
SELECT
[AGENT_URN],
[QUEUE_URN],
[ACTIVITY_ID]
FROM #ChatActivityStateDetail
WHERE DATE_TIME_UTC between @pDate_From and @pDate_To
and not exists (select 1 from #ConferenceChatDetail a where a.ACTIVITY_ID = #ChatActivityStateDetail.ACTIVITY_ID)
--and ACTIVITY_ID not in (select ACTIVITY_ID from #ConferenceChatDetail)
如果它能解决您的问题,请标记为答案。谢谢。
Mark as answer if it resolves your problem. Thanks.
这篇关于SQL:除非与否的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!