CODEWITHSUNDEEP
sql-server
ihorko
ihorko

Reputation: 6945

SQL Server : check if all rows exists in other table

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.

What should I change in my query? Thanks!

Upvotes: 9

Views: 17134

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460268

You could use EXCEPT to get the set difference of both tables. If any ID's are returned, both tables are not equal:

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.

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

Upvotes: 3

John Dewey
John Dewey

Reputation: 7093

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

Upvotes: 9

therealmitchconnors
therealmitchconnors

Reputation: 2760

This would work as long as both id columns are unique (which they should be if they are id's)

DECLARE @totalRows int;
SET @totalRows = SELECT count(*) from Table1;

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

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

Upvotes: 0

Related Questions