Pick distinct rows based on two tables T-SQL

Question

Consider the tables in image

How to select rows from Table1 excluding items from Table2 with same ReferenceId?

The result should be

Answer 1

You can JOIN the tables on ReferenceId, using a LEFT OUTER join and restrict the return values to where the ReferenceId is NULL on Table2.

SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ReferenceId = Table2.ReferenceId
WHERE Table2.ReferenceId IS NULL

Answer 2

You can use something like:

SELECT  Id
        ,ReferenceId
FROM    Table1
WHERE   ReferenceId NOT IN (SELECT DISTINCT ReferenceId FROM Table2);