Compare tables and avoid duplicates

Transact-SQL
1

Hi All.
How to modify this select

SELECT T1.*
FROM dbo.Table1 T1
WHERE NOT EXISTS (
SELECT 1
FROM dbo.Table2 T2
WHERE T2.Employee_ID = T1.Employee_ID AND T2.Department_ID = 9
)

to retrieve columns from both table and avoid duplicates?

Thanks.

2

what are the keys on T1 and T2? Also, "to retrieve columns from both table and avoid duplicates" doesn't work with not exists

3

Hi mike01. Thanks for reply.
T1 primary key Employee_ID. T2 primary key say ID, foreign key Employee_ID.

Thanks.

4

What do you consider duplicates? If you join the tables on EmployeeID - and Table2 has multiple departments identified for a single employee - which one do you want?

It is going to depend on what you have determined are duplicate entries. Once you have that - you can use a CTE for the main query, adding a row_number() function to identify the 'duplicate' rows and then select from the CTE where the row number = 1.

If it is more complex than that - then you might need a CROSS APPLY with a SELECT TOP 1...ORDER BY to get the correct non-duplicate.