I need to know if all rows from one table exists in other:
我需要知道一个表中的所有行是否都存在于其他表中:
declare @Table1 table (id int)
declare @Table2 table (id int)
insert into @Table1(id) values (1)
insert into @Table1(id) values (4)
insert into @Table1(id) values (5)
insert into @Table2(id) values (1)
insert into @Table2(id) values (2)
insert into @Table2(id) values (3)
if exists (select id from @Table1 where id in (select id from @Table2))
select 'yes exists'
else
select 'no, doesn''t exist'
This query returns yes exists
but should return no, doesn't exist
because only 1 exists in @Table2
, values 4 and 5 don't.
此查询返回yes存在,但应返回no,不存在,因为@ Table2中只存在1,值4和5不存在。
What should I change in my query? Thanks!
我的查询应该更改什么?谢谢!
4 个解决方案
#1
8
IF NOT EXISTS (
SELECT ID FROM @Table1
EXCEPT
SELECT ID FROM @Table2
)
SELECT 'yes exists'
ELSE SELECT 'no, doesn''t exist'
#2
2
You could use EXCEPT
to get the set difference of both tables. If any ID's are returned, both tables are not equal:
您可以使用EXCEPT来获取两个表的设置差异。如果返回任何ID,则两个表不相等:
SELECT ID
FROM @Table1
EXCEPT
SELECT ID
FROM @Table2
EXCEPT returns any distinct values from the left query that are not also found on the right query.
EXCEPT返回左查询中的任何不同值,这些值在右侧查询中也找不到。
So, to get your "no, doesnt exist":
所以,要得到你的“不,不存在”:
;WITH diff AS(
SELECT ID
FROM @Table1
EXCEPT
SELECT ID
FROM @Table2
)
SELECT CASE WHEN COUNT(diff.ID) = 0
THEN 'yes exists'
ELSE 'no, doesnt exist'
END AS Result
FROM diff
#3
0
select case when count(*) > 0 then 'no' else 'yes' end as AllExist
from @Table1 t1
left outer join @Table2 t2 on t1.id = t2.id
where t2.id is null
#4
0
This would work as long as both id columns are unique (which they should be if they are id's)
只要两个id列都是唯一的(如果它们是id,它们应该是这样的),这将起作用
DECLARE @totalRows int;
SET @totalRows = SELECT count(*) from Table1;
RETURN (@totalRows == SELECT count(*) from Table1 JOIN Table2 on Table1.id = Table2.id)
#1
8
IF NOT EXISTS (
SELECT ID FROM @Table1
EXCEPT
SELECT ID FROM @Table2
)
SELECT 'yes exists'
ELSE SELECT 'no, doesn''t exist'
#2
2
You could use EXCEPT
to get the set difference of both tables. If any ID's are returned, both tables are not equal:
您可以使用EXCEPT来获取两个表的设置差异。如果返回任何ID,则两个表不相等:
SELECT ID
FROM @Table1
EXCEPT
SELECT ID
FROM @Table2
EXCEPT returns any distinct values from the left query that are not also found on the right query.
EXCEPT返回左查询中的任何不同值,这些值在右侧查询中也找不到。
So, to get your "no, doesnt exist":
所以,要得到你的“不,不存在”:
;WITH diff AS(
SELECT ID
FROM @Table1
EXCEPT
SELECT ID
FROM @Table2
)
SELECT CASE WHEN COUNT(diff.ID) = 0
THEN 'yes exists'
ELSE 'no, doesnt exist'
END AS Result
FROM diff
#3
0
select case when count(*) > 0 then 'no' else 'yes' end as AllExist
from @Table1 t1
left outer join @Table2 t2 on t1.id = t2.id
where t2.id is null
#4
0
This would work as long as both id columns are unique (which they should be if they are id's)
只要两个id列都是唯一的(如果它们是id,它们应该是这样的),这将起作用
DECLARE @totalRows int;
SET @totalRows = SELECT count(*) from Table1;
RETURN (@totalRows == SELECT count(*) from Table1 JOIN Table2 on Table1.id = Table2.id)