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)
'# 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) |
