SQL != ALL() 难度

Question

对于我试图为 Oracle SQL 数据库编写的查询，我只是试图从 CI 表中获取所有由某人/某物拥有的行，这些行未列在 sys_user 表中，但返回的结果是由手动检查以下查询实际上在 sys_user 表中找到。更奇怪的是，owner_id 的所有返回值都是相同的。

SELECT
  ci.sys_id as product_id,
  ci.name as product,
  ci.sys_class_name as class_name,
  ci.owned_by as owner_id
FROM 
    EDQ_EDW.CMDB_CI CI,
  EDQ_EDW.CMDB_SYS_USER usr
WHERE
    ci.owned_by is not null
  and ci.owned_by != all(usr.sys_id)

我已经仔细检查了 Oracle 文档，以确保我没有错误地使用 != ALL()，我不相信我是。这是什么原因造成的？

Answer 1

ALL、SOME 和ANY 条件适用于行集，而不是您在查询中使用的标量值。尝试这样的事情:

SELECT
  ci.sys_id as product_id,
  ci.name as product,
  ci.sys_class_name as class_name,
  ci.owned_by as owner_id
FROM EDQ_EDW.CMDB_CI CI
WHERE ci.owned_by is not null
  AND ci.owned_by != ALL (SELECT sys_id FROM EDQ_EDW.CMDB_SYS_USER)

我不确定逻辑是否正确，但语法应该没问题。如果您没有得到想要的结果，请尝试 NOT IN:

SELECT
  ci.sys_id as product_id,
  ci.name as product,
  ci.sys_class_name as class_name,
  ci.owned_by as owner_id
FROM EDQ_EDW.CMDB_CI CI
WHERE ci.owned_by is not null
  AND ci.owned_by NOT IN (SELECT sys_id FROM EDQ_EDW.CMDB_SYS_USER)

您还可以使用 NOT EXISTS 或“左连接并检查 NULL”查询。这些在某些情况下更快。您可以使用简单的示例找到解释 here .