clear markdown compare help best fiddles feedback a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1716848 fiddles created (23945 in the last week).

create table t1(id int, name varchar(10), ssn int); create table t1_exclusive(id int);
 hidden batch(es)

insert into t1(id,name,ssn) values (1,'A',111) ,(2,'B',222) ,(3,'C',111) ,(4,'D',333) ,(5,'E',444) ,(6,'F',555) ,(7,'G',222) ,(8,'H',777) ,(9,'I',888) ,(10,'J',999)
10 rows affected
 hidden batch(es)

insert into t1_exclusive(id) values(2)
1 rows affected
 hidden batch(es)

declare @ssn int = null; With t as ( select,, t1.ssn, sign( as Is_Exclusive , max(sign( over (Partition by t1.ssn) as Has_Exclusive from t1 left join t1_exclusive on = ) Select id,name,ssn From t where @ssn is null or (ssn = @ssn and ((Is_Exclusive is null and Has_Exclusive is null) or (Is_Exclusive is not null and Has_Exclusive is not null)))
id name ssn
3 C 111
1 A 111
2 B 222
7 G 222
4 D 333
5 E 444
6 F 555
8 H 777
9 I 888
10 J 999
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)