SQL Server: check if all rows in another table exist

Question

I need to know if all rows from one table exist in another:

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 do not.

What should I change in my request? Thanks!

Answer 1

 IF NOT EXISTS ( SELECT ID FROM @Table1 EXCEPT SELECT ID FROM @Table2 ) SELECT 'yes exists' ELSE SELECT 'no, doesn''t exist' 

Answer 2

You can use EXCEPT to get the given difference in both tables. If any identifier is returned, both tables are not equal:

 SELECT ID FROM @Table1 EXCEPT SELECT ID FROM @Table2 

EXCEPT returns any single values ​​from the left query that are also not found in the correct query.

So, to get "no does not 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 

Answer 3

 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 

Answer 4

This will work as long as both id columns are unique (they should be if they are identifiers)

 DECLARE @totalRows int; SET @totalRows = SELECT count(*) from Table1; RETURN (@totalRows == SELECT count(*) from Table1 JOIN Table2 on Table1.id = Table2.id)