试试这个：

/*
WITH 
  TYPE (ID, TYPE) AS 
(
VALUES
  ('01', 'CAT')
, ('02', 'DOG')
, ('03', 'CAT')
)
, NAME (ID, NAME) AS 
(
VALUES
  ('01', 'MIFF')
, ('02', 'RUFF')
--, ('03', 'IFF') 
, ('03', '')
)

* /

SELECT T1.ID ID1, T2.ID ID2
FROM TYPE T1
JOIN TYPE T2 ON T2.TYPE = T1.TYPE AND T2.ID > T1.ID
JOIN NAME N1 ON N1.ID = T1.ID
JOIN NAME N2 ON N2.ID = T2.ID
WHERE N1.NAME <> N2.NAME;

你可以在下面试试-

select min(i.id) as duplicated1, max(i.id) as duplicated2 from idtable i inner join typetable t on i.id=t.id
where type=
(select typeval 
from types inner join name on types.id=name.id
group by typeval
having count(distinct types.id)>1 and count(distinct coalesce(nameval,'xxx'))>1
)

这回答了问题的原始版本。
如果我理解正确，您可以使用窗口函数：

select t.*
from (select t.*,
             min(n.name) over (partition by t.type) as min_name,
             max(n.name) over (partition by t.type) as max_name,
             sum(case when n.name is null then 1 else 0 end) over (partition by t.type) as num_nulls
      from type t join
           name n
           on t.id = n.id
    ) tn
where min_name <> max_name or num_nulls > 0;

也就是说，您的数据模型似乎已关闭。您应该有一个表，每种类型一行。应该有一个与类型关联的有效名称。