select * from 产品库 where 产品编号 int (2,3,4,5,7,9)有些在in里面在"产品库"中没有的,我要怎么才那知道那些没有的

create table tb(id int,name varchar(10))

insert tb

select 1,'a' union all

select 2,'b' union all

select 5,'c' union all

select 6,'d' union all

select 7,'e' union all

select 8,'f' union all

select 9,'g'

go

declare @s varchar(1000)

set @s='2,3,4,5,7,9'

set @s = replace(@s,',', ' as id union all select ' )



exec('select * from (select '+@s+') aa 

where not exists(select 1 from  tb t where aa.id = t.id and t.id in(2,3,4,5,7,9))')





drop table tb

/*



id          

----------- 

3

4

*/

--结果

在@t表中没有的	1	a

在@t表中没有的	6	d

在@t表中没有的	8	f

在@s表中没有的	3	NULL

在@s表中没有的	4	NULL

declare @s table(id int,name varchar(10))

insert @s

select 1,'a' union all

select 2,'b' union all

select 5,'c' union all

select 6,'d' union all

select 7,'e' union all

select 8,'f' union all

select 9,'g'



--创建临时表记录你要查询产品的ID

declare @t table(id int)

insert @t

select 2 union all

select 3 union all

select 4 union all

select 5 union all

select 7 union all

select 9





select bz='在@t表中没有的',a.id,a.name from @s a where not exists(select id from @t where id=a.id)

union all 

select bz='在@s表中没有的',b.id ,null from @t b where not exists(select id from @s where id=b.id)

create table tb(产品编号 int)

insert into tb values(2)

insert into tb values(5)

insert into tb values(7)

insert into tb values(9)

go



select 产品编号 from

(

  select 2 产品编号 union select 3 union select 4

  union select 5 union select 7 union select 9

) t

where 产品编号 not in

(

  select distinct 产品编号 from tb where 产品编号 in (2,3,4,5,7,9)

)



drop table tb



/*

产品编号        

----------- 

3

4



（所影响的行数为 2 行）

*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[f_splitSTR]

GO



--3.2.1 循环截取法

CREATE FUNCTION f_splitSTR(

@s   varchar(8000),   --待分拆的字符串

@split varchar(10)     --数据分隔符

)RETURNS @re TABLE(col varchar(100))

AS

BEGIN

	DECLARE @splitlen int

	SET @splitlen=LEN(@split+'a')-2

	WHILE CHARINDEX(@split,@s)>0

	BEGIN

		INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))

		SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')

	END

	INSERT @re VALUES(@s)

	RETURN

END

GO





declare @Product table(id int,name varchar(10))

insert @Product

select 1,'a' union all

select 2,'b' union all

select 5,'c' union all

select 6,'d' union all

select 7,'e' union all

select 8,'f' union all

select 9,'g'



declare @s nvarchar(100)

set @s='2,3,4,5,7,9'

select 

	a.*

from 

	f_splitSTR(@s,',') a

left join

	@Product b on a.Col=b.ID

where

	b.ID is null







（所影响的行数为 7 行）



col                                                                                                  

---------------------------------------------------------------------------------------------------- 

3

4



（所影响的行数为 2 行）

----创建产品库

declare @Product table(id int,name varchar(10))

insert @Product

select 1,'a' union all

select 2,'b' union all

select 5,'c' union all

select 6,'d' union all

select 7,'e' union all

select 8,'f' union all

select 9,'g'



----将要查询的ID添加到临时表中

declare @t table(id int)

insert @t

select 2 union all

select 3 union all

select 4 union all

select 5 union all

select 7 union all

select 9



----查询

select a.* from @t as a LEFT JOIN @Product as b on a.id = b.id where b.id IS NULL



/*结果

id          

----------- 

3

4

*/

create table tb (id int,name varchar(8000))

insert into tb select 2,'aaa'

insert into tb select 5,'aaa'

insert into tb select 9,'aaa'



select tempid from (

select * from 

(select 1 as tempid 

union all select 2

union all select 3

union all select 4

union all select 5

union all select 6

union all select 7

union all select 8

union all select 9) a 

left join 

tb b on  a.tempid=b.id )c where c.id is null

select a.id from 

  (select id=2

  union all select 3

  union all select 4

  union all select 5

  union all select 7

  union all select 9) a

left join join

  (select distinct 产品编号 id from tb where 产品编号 in(2,3,4,5,7,9)) b

  on a.id=b.id 

  where b.id is null

select   *   from   产品库   where     产品编号   in   (2,3,4,5,7,9)

select   *   from   产品库   where     产品编号   not in   (2,3,4,5,7,9)