如何显示表中不存在的子句的元素 [英] How Can I Show Elements Of Where Clause That Are Not Present In Table

问题描述

表

Table

ID
1234
3456
7890

SELECT *
FROM Table
WHERE ID IN (1234,9999,8888)

如果ID在where子句中不在表中，则显示那些ID。

If ID is in where clause are not in table, then show those ids.

Ouput
9999,8888

推荐答案

一种简单的方法是使用 EXCEPT 从所有值中删除现有值。请考虑以下示例

A simple way would by to use EXCEPT to remove existing values from all values. Consider the following example

SELECT all_values.Id
FROM (SELECT 1234 AS Id UNION ALL
      SELECT 9999 AS Id UNION ALL
      SELECT 8888 AS Id ) all_values
EXCEPT
SELECT Id
FROM MyTable

有关详细信息，请参阅：EXCEPT和INTERSECT（Transact-SQL） [ ^ ]

我使用cte：

I'd use a cte:

with mycte as (
  select 1234 as id,
  union select 9999,
  union select 8888)

select m.id 
from mycte m
where not exists (select * from table t where m.id = t.id)

似乎还有很长的路要走，但它非常可扩展。



希望它至少会让你朝着新的方向发展

it seems a long way around but it's VERY scaleable.

Hopefully it will at least push you in a new direction

可以有更好的方法来做到这一点但是检查这个和还等待一些更好的答案。

我用 除了

There can be better ways to do this but check this and also wait for some better answers.
I have used EXCEPT

SELECT * FROM 
(
	SELECT 1234 AS ID
	UNION 
	SELECT 9999
	UNION
	SELECT 8888
) T1
EXCEPT
SELECT * FROM
(
	SELECT 1234 AS ID
	UNION
	SELECT 3456
	UNION
	SELECT 7890
) AS T2

希望，它有助于：）

Hope, it helps :)

这篇关于如何显示表中不存在的子句的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！