Use tempdb Go If object_id ('Table _ left') is not null drop table table_left If object_id ('Table _ right') is not null drop table table_right Go Create table table_left (groupId nvarchar (5), dataSub1 nvarchar (10), dataSub2 nvarchar (10 )) Create table table_right (groupId nvarchar (5), dataSub1 nvarchar (10), dataSub2 nvarchar (10 )) Go
Alter table table_left add dataChecksum as checksum (dataSub1, dataSub2) Alter table table_right add dataChecksum as checksum (dataSub1, dataSub2) Go
Create nonclustered index ix_table_left_cs on table_left (dataChecksum) Create nonclustered index table_right_cs on table_right (dataChecksum) Go Set nocount on Go Insert into table_right (groupId, dataSub1, dataSub2) Select '# 1', 'data1', 'data7' union all Select '# 1', 'data2', 'data8' union all Select '# 1', 'data3', 'data9' union all Select '# 2', 'data55', 'data4' union all Select '# 2', 'data55', 'data5'
Insert into table_left (groupId, dataSub1, dataSub2) Select '# 11', 'data1', 'data7' union all Select '# 11', 'data2', 'data8' union all Select '# 11', 'data3', 'data9' union all Select '# 22', 'data55', 'data0' union all Select '# 22', 'data57', 'data2' union all Select '# 33', 'data99', 'data4' union all Select '# 33', 'data99', 'data6'
Go -- Select
Select distinct a. groupId From table_left Inner join table_right B on B. dataChecksum = a. dataChecksum And B. dataSub1 = a. dataSub1 And B. dataSub2 = a. dataSub2 Where not exists (select x. dataSub1, x. dataSub2 from table_left x where x. groupId =. groupId limit T select y. dataSub1, y. dataSub2 from table_right y where y. groupId = B. groupId) And not exists (select x. dataSub1, x. dataSub2 from table_right x where x. groupId = B. groupId limit T select y. dataSub1, y. dataSub2 from table_left y where y. groupId =. groupId) |