Joining Tables With Different Collation

If you have two tables from two different databases each has a collation different than the other and you tried to join the two tables in one query...like

SELECT * FROM

      DB1..table1 LEFT JOIN DB2..table2

      on table1.id = table2.id

where the collation for DB1 is SQL_Latin1_General_CP1_CI_AS, and for DB2: Latin1_General_BIN
you will get the following error:

Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

to solve this in an easy way just modify it as:

SELECT * FROM

      DB1..table1 LEFT JOIN DB2..table2

      on table1.id = table2.id COLLATE SQL_Latin1_General_CP1_CI_AS

or you can set it to the default database collation, like:

SELECT * FROM

      DB1..table1 LEFT JOIN DB2..table2

      on table1.id = table2.id COLLATE database_default

There is another way in a blog, in a post titled "How to create SQL Server temp tables without collation problems", but i see this one easier, more practical, and faster.