Return true if all records of the first table exist in the second table
I have two tables:
declare @t1 table (id int)
declare @t2 table (id int)
insert into @t1
select 1 union select 3 union select 7
insert into @t2
select 1 union select 3 union select 7 union select 9 union select 4
select count(*) from @t1 t inner join @t2 t1 on t.id = t1.id
I get the result for the above query as 3. I need true or false if all records in t1 exist in t2.
this is a simplified example of a real table structure. real tables can have millions of records, so please let me know some optimized way to do this.
+3
Vikram
source
to share
5 answers
SELECT CASE
WHEN EXISTS (SELECT id
FROM @t1
EXCEPT
SELECT id
FROM @t2) THEN 0
ELSE 1
END
+2
Martin Smith
source
to share
declare @t1 table (id int)
declare @t2 table (id int)
insert into @t1
select 1 union select 3 union select 7
insert into @t2
select 1 union select 3 union select 7 union select 9 union select 4
if exists(
select id from @t2
except
select id from @t1
) print 'false'
else print 'all the records in t1 exists in t2'
+1
Diego
source
to share
Usage exists
(might be more efficient):
select
case
when not exists (select 1
from @t1 t1
where not exists(select 1 from @t2 t2 where t2.id = t1.id))
then cast(1 as bit)
else cast(0 as bit)
end
+1
Kirill Polishchuk
source
to share
SELECT (CASE WHEN
(SELECT COUNT(*) from t1 where
not id IN (select id from t2)) = 0 THEN
convert(bit, 1)
ELSE convert(bit, 0) END)
+1
Amen ayach
source
to share
Comparing the number of matched lines to full lines in @t1
may be more efficient. Sometimes, you just need to try several methods and look at the query plans to see which one works best for your situation. You will need multiple test tables with similar amounts of data and corresponding indexes, etc.
declare @t1 table (id int)
declare @t2 table (id int)
insert into @t1
select 1 union select 3 union select 7
insert into @t2
select 1 union select 3 union select 7 union select 9 union select 4
select case
when (select count(*) from @t1 t join @t2 t1 on t.id = t1.id) =
(select count(*) from @t1) then 1 else 0
end as rows_match
+1
Dave carlile
source
to share