SQL Server - Need Join but where NOT Equal To [clo

问题:

I am using SQL Server 2012.

I know how to do an inner join which gives one where there is a match. I also need to get the records where there was not match.

What is the best approach. I guess I can do a left join and an inner join and then get the ones that are left behind. Wondering what the best and cleanest approach would be.

As mentioned, I am already doing an inner join but also need to show records where there was no match.

回答1:

You probably are looking for an outer join or an outer excluding join.

OUTER JOIN

SELECT *
FROM tableA a
FULL OUTER JOIN tableB b
    ON a.column = b.column

OUTER EXCLUDING JOIN

SELECT *
FROM tableA a
FULL OUTER JOIN tableB b
    ON a.column = a.column
WHERE a.column IS NULL OR b.column IS NULL

The graphs in this answer are taken from this very useful article.

回答2:

If you want to get values from both tables, you can use full outer join and take records which have one side as null:

select a.*, b.* from tableA a
full outer join tableB b on a.col = b.col
where a.col is null or b.col is null

Obviously this way all the values for either one table or the other will be null.