Get rows having different values for a column based on the duplicate values of combination of other 3 columns

I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.

Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output.

But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.

rows 5 and 6 are not to be listed since they are single row with different values.

+----------+-------+--------+--------+
| dept     | role1 |role2   |DEF     |
+----------+-------+--------+--------+
| a        | abc   | er     | 0      |
| a        | abc   | er     | 1      |
| b        | qwer  | ty     | 0      |
| b        | qwer  | ty     | 0      |
| c        | der   | ui     | 1      |
| d        | nerr  | io     | 0      |
+----------+-------+--------+--------+
output

+----------+------+------+------+
| dept     | role1|role2 |DEF   |
+----------+------+------+------+
| a        | abc  | er   |0     |
| a        | abc  | er   |1     |
+----------+------+------+------+

I tried using distinct with having but not able to check the values of column DEF to get desired result.

Can anyone help me on this?

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) > 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 <> 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 <> d.DEF
) ca
;

CROSS APPLY works with Oracle or SQL Server.

Output:

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

StackExchange Database Administrators Q#132405, answer score: 18