SQL - NOT IN 解释
我正在从事一个需要在 SQL 结果中获得最高性能的项目,并且正在寻求优化查询,但经过一些尝试和错误后,我在 IN 方面遇到了一些问题。
-- THIS RETURNS NO RESULTS AT ALL.
SELECT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY
where TICKETID = 't6UJ9A002MJC'
-- THIS RETURNS 4 RESULTS.
SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS
where sysdba.C_TICKETPROVIDERS.TICKETID = 't6UJ9A002MJC'
-- THIS RETURNS NO RESULTS AT ALL.
SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS
where sysdba.C_TICKETPROVIDERS.TICKETID NOT IN
(SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY)
由于没有匹配结果,最后一个查询结果不应该至少与前一个查询结果有4个相同的结果吗???
I am working in a project which needs top performance in SQL results, and was looking to optimize a query, but after some trial and error I am having some trouble with IN.
-- THIS RETURNS NO RESULTS AT ALL.
SELECT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY
where TICKETID = 't6UJ9A002MJC'
-- THIS RETURNS 4 RESULTS.
SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS
where sysdba.C_TICKETPROVIDERS.TICKETID = 't6UJ9A002MJC'
-- THIS RETURNS NO RESULTS AT ALL.
SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS
where sysdba.C_TICKETPROVIDERS.TICKETID NOT IN
(SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY)
Shouldn't the last query result at least the same 4 results as the previous one, since there are no matching results???
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
“SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY”返回 null,因此最后一个查询不返回任何行。无法与这样的空结果进行比较。使用“不存在”。
"SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY" returns null, hence the last query doesnt return any rows. Cant compare against null result like that. Use "NOT EXISTS".
WHERE
子句中的NOT IN (NULL)
部分返回 false。尝试使用:
1
,并且NOT EXISTS
子句失败,正如预期的那样。NULL
,并且NOT EXISTS
将成功。The piece
NOT IN (NULL)
in yourWHERE
clausole returns false.Try with:
1
, and theNOT EXISTS
clausole fails, as expected.NULL
, and theNOT EXISTS
will succeed.只是为了好玩,试试这个:
如果这有效,你的问题是在你的平台上,
Not In
将无法针对空集正常运行...Just as a lark, try this:
If this works, yr problem is that on your platform,
Not In
will not function properly against an EMpty set...查询
(SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY)
返回NULL
。您有:
勾选 id 1 is NOT IN (NULL)? FALSE
勾选 id 2 不在 (NULL)? FALSE
勾选 id 3 不在(NULL)? FALSE
....等等...
没有记录可以匹配您的where条件
NOT IN
。所以你根本得不到任何结果。尝试使用以下查询:
The query
(SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY)
returnNULL
.You have:
ticked id 1 is NOT IN (NULL)? FALSE
ticked id 2 is NOT IN (NULL)? FALSE
ticked id 3 is NOT IN (NULL)? FALSE
....and so on....
No record can match your where condition
NOT IN
. So you get NO RESULTS AT ALL.Try with this query:
sysdba.History.TicketID 和 sysdba.C_TICKETPROVIDERS.TICKETID 在数据类型和长度方面是否匹配?
我还发现 group by 比 unique 更快。
最后检查数据库的排序规则类型,排序规则可能区分大小写,并且由于某种原因一个是大写的,另一个不是大写的?
您能提供表架构吗?
Do the sysdba.History.TicketID and sysdba.C_TICKETPROVIDERS.TICKETID match in terms of datatype and length?
I've also found group by's to be quicker than distinct.
Lastly check the collation types for the database, it might be that the collation is case sensitive and for some reason one is uppercased and the other isnt?
Can you provide the table schemas?