Get rows having different values for a column based on the duplicate values of combination of other 3 columns
Using standard SQL on most RDBMS, there are various ways.

Using a subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM data d
INNER JOIN (
    SELECT dept, role1, role2 
    FROM data
    GROUP BY dept, role1, role2
    HAVING COUNT(distinct DEF) &gt; 1
) dup
    ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2
;


The subquery returns sets of dept/role1/role2 with more than 1 distinct DEF.

Using a correlated subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM @data d
WHERE EXISTS (
    SELECT 1 
    FROM @data 
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF &lt;&gt; d.DEF
);


The subquery return 0 to n rows. If at least one row exists, the row from the main table is returned.

Using CROSS APPLY:

SELECT d.dept, d.role1, d.role2, d.DEF
FROM @data d
CROSS APPLY (
    SELECT n=1 
    FROM @data 
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF &lt;&gt; d.DEF
) ca
;


CROSS APPLY works with Oracle or SQL Server.

Output:

dept    role1   role2   DEF
a       abc     er      0
a       abc     er      1