SQL Server query to select only values not in a list-CodePudding

As an example I have a table in SQL Server :

and I have a list of values 1,2,3

What is the query to select values of my list not in the table.

Expected result :

Id
2

CodePudding user response：

Here are two approaches

Using Not Exists

Select *
 from  string_split('1,2,3',',') A
 Where not exists ( select 1 from YourTable where ID=Value )

Using a LEFT JOIN

Select A.Value
 from  string_split('1,2,3',',') A
 left Join  YourTable B on A.value=B.ID
 Where B.ID is null

Both Results are

Value
2

CodePudding user response：

SELECT * FROM id_table WHERE id NOT IN (1,2,3);