SQL查询中的WHERE子句

Question

我需要找出WHERE IN子句中在数据库中不存在的项。在下面的例子中，cc33不存在，我需要查询返回cc33。该怎么做?

SELECT id FROM tblList WHERE field1 IN ('aa11','bb22','cc33')

- pirmas naujas

---

每个字段1都会有多个ID吗？ - Matt Busche

Answer 1

您需要将值放入表格中而不是列表中：

with list as (
    select 'aa11' as val union all
    select 'bb22' union all
    select 'cc33'
)
select l.val
from list l left outer join
     tbllist t 
     on l.val = t.field1
where t.field1 is null

- Gordon Linoff

---

为什么不使用 SELECT COALESCE(t.id, l.val)？（无论如何加1） - zerkms

我原本打算做3个单独的JOIN，但这样做更好。即使没有CTE，我们也可以使用相同逻辑的UNION作为子查询。 - PM 77-1

@zerkms . .. where语句明确检查t上的所有值是否为NULL。这样的合并在最好的情况下是多余的，在最坏的情况下会导致id和val类型不一致的类型冲突。 - Gordon Linoff

这两个答案都可以工作。哪一个性能更好？这些aa11，bb22只是测试值，我将在这里放置成千上万个值。 - pirmas naujas

@pirmasnaujas . . . 性能可能无论哪种方式都足够快。这里有一个参考，它说“NOT IN”提供了更好的性能（http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/）。请注意，所研究的案例规模更大，包括重复项。如果性能真的很重要，您应该在自己的数据上进行测试。 - Gordon Linoff

我不得不稍微修改你的查询，因为我的旧sql2000不接受WITH子句。无论如何，这是它的工作原理：select l.val from ( select 'aa11' as val union all select 'bb22' union all select 'cc33' ) l left outer join tbllist t on l.val = t.field1 where t.field1 is null - pirmas naujas

Answer 2

对于SQL Server 2008+版本，您可以使用表值构造函数：

SELECT field1
FROM
  ( VALUES
     ('aa11'),('bb22'),('cc33')
  ) AS x (field1)
WHERE field1 NOT IN
      ( SELECT field1 FROM tblList ) ;

在SQL-Fiddle上测试过。

- ypercubeᵀᴹ

---

这个查询也可以工作，但我无法在SQL2000中使其工作。 - pirmas naujas

是的，它是在2008版本中添加的。它无法在2000或2005版本中工作。 - ypercubeᵀᴹ

对于之前的版本，您将不得不坚持使用Gordon回答的UNION版本。 - ypercubeᵀᴹ