clear markdown compare help best fiddles feedback dbanow.uk 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.id, t1.name, t1.ssn, sign(t1_exclusive.id) as Is_Exclusive , max(sign(t1_exclusive.id)) over (Partition by t1.ssn) as Has_Exclusive from t1 left join t1_exclusive on t1_exclusive.id = t1.id ) 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)