Clever Geek Handbook

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


source to share


5 answers 


SELECT CASE 
         WHEN EXISTS (SELECT id 
                      FROM   @t1 
                      EXCEPT 
                      SELECT id 
                      FROM   @t2) THEN 0 
         ELSE 1 
       END


+2


source 


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


source


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


source 


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


source


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


source






More articles:


All Articles