从表中选择不在列表SQL中的值

If I type:

如果我输入:

SELECT name FROM table WHERE name NOT IN ('Test1','Test2','Test3');

I can get the entries from the table that are not in the list. I want to do the opposite: Get the values from the list that are not in the table. For example, if table has a column named name that has the values 'Test1' and 'Test3' I want to compare that to ('Test1','Test2','Test3') and return Test2. Or as another example, if the table is empty, then return everything in the list: Test1, Test2, and Test3.

我可以从表中获取不在列表中的条目。我想做相反的事情:从列表中获取不在表中的值。例如,如果table有一个名为name的列,其值为'Test1'和'Test3',我想将其与('Test1','Test2','Test3')进行比较并返回Test2。或者作为另一个示例,如果表为空,则返回列表中的所有内容:Test1,Test2和Test3。

Is there a way to do this WITHOUT creating a new table with all of the values in the list?

有没有办法在没有创建包含列表中所有值的新表的情况下执行此操作?

4 个解决方案

#1

Depending on how many values you have, you could do a few unions.

根据你拥有的价值,你可以做几个工会。

See: http://www.sqlfiddle.com/#!5/0e42f/1

select * from (
  select 'Test 1' thename union
  select 'Test 2' union 
  select 'Test 3'
)
where thename not in (select name from foo)

#2

I usually use SELECT 'FOO' AS COL UNION SELECT 'BAR' etc and then use the standard idiom of left joining and checking for NULL to find missing elements.

我通常使用SELECT'FOO'AS COL UNION SELECT'BAR'等,然后使用左连接的标准惯用法并检查NULL以查找缺少的元素。

CREATE TABLE #YourTable(
name nvarchar(50)
)

insert into #YourTable (name) values ('Test1'), ('Test3')

-- ALL
select * from #YourTable

--MISSING
select t1.* from (
  select 'Test1' testName
  union select 'Test2'
  union select 'Test3') as t1
  left outer join #YourTable yt on t1.testName = yt.name
  where yt.name is null

DROP TABLE #YourTable

Gives output

name
--------------------------------------------------
Test1
Test3

(2 row(s) affected)

testName
--------
Test2

(1 row(s) affected)

#3

Select a.value from (
SELECT 'testvalue' value UNION
SELECT 'testvalue2' value UNION
SELECT 'testvalue3' value UNION
SELECT 'testvalue4' value UNION
) a
left outer join othertable b
on a.value=b.value
where b.value is null

This is perfect for my problem without temp table#

对于没有临时表的问题,这是完美的#

#4

Assuming "othertable" holds the table in question...

假设“othertable”持有有问题的表...

 select a.value from 
    (select 'test1' value
     union
     select 'test2' value
     union 
     select 'test3' value) a
       left outer join othertable b
         on a.value=b.value
      where b.value is null

#1