SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1(id int, name varchar(10), ssn int);
create table t1_exclusive(id int);
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

insert into t1_exclusive(id) values(2)
1 rows affected

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