Output all duplicate rows (SQL Server)

Question

I have a table which holds what I consider duplicate rows. the values in these records may not be exactly the same, but it’s been calculated that they’re possible duplicates by fuzzy logic. For example:

RecordCD    key_in  key_out
---------------------------
1           1       2
2           2       2
3           3       3
4           4       6
5           5       5
6           6       6
7           7       7
8           8       11
9           9       9
10          10      10
11          11      11

key_in column has a unique ID of the record.

key_out column has a possible duplicate if it’s not equal to key_in

I need my output to look like this and list all of the possible duplicates:

RecordCD    key_in  key_out
---------------------------
1           1       2
2           2       2
4           4       6
6           6       6
8           8       11
11          11      11  

but I’m struggling to construct a query that would do that.

Thanks.

Answer 1

I think this is what you want:

select t.*
from t
where exists (select 1
              from t t2
              where t2.key_out = t.key_out and t2.key_in <> t.key_in
             )
order by t.key_out;

Here is a db<>fiddle.

Answer 2

It seems like if there is a mismatch between key_in, key_out you want to pull all rows where key_in has either value`

I would create a temp table with all values in rows with mismatched key_in, key_out, call this value bad_match

If either of your key_in, key_out values match this value, include it in output

select mytable.* from mytable 
where key_in  in 
(select key_in bad_match from mytable where key_in <> key_out
union all
select key_out from mytable where key_in <> key_out);

This sample builds your schema and returns the desired output