Menu
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.
955
A lot of SQL beginners use equi JOINs and don't even know that you can use a non-equality condition in JOIN.
Non-Equi Join is also a type of INNER Join in which we need to retrieve data from multiple tables. Non-Equi Join matches the column values from different tables based on an inequality based on the operators like <,>,<=,>=,!= , BETWEEN, etc.
No, inner joins can have non equi join conditions. There are basically 2 components in any join, one is the join type (can be inner, left outer, right outer, full outer etc..) and the other is how join is performed (can be equi join and non equi join).
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.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
