SQL server collation concept in join?

Question

Why having consistent collation is important when you use joins?

I've used collation while joining two tables across two databases but why we need it? If you've same collation on both tables on two different database do we need it?

Answer 1

Collations define rules for string sorting and comparison - msdn.

Personally, I use this query style -

SELECT * 
FROM dbo.Table1 t1
JOIN dbo.Table2 t2 ON t1.KeyCD COLLATE DATABASE_DEFAULT = t2.KeyCD COLLATE DATABASE_DEFAULT