Comparing between different collations

In MSSQL 2005, when comparing string (nvarchar, varchar, etc..) between different collations e.g.
SELECT * FROM sys.sql_logins
WHERE NAME NOT IN (
SELECT login_name FROM RESOURCE
)

SQL Server would throw an error due to different collations set for the columns compared:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

So, to be able to compare between different collations, COLLATEE DATABASE_DEFAULT can be used, which would convert the column(s) to the collation the current database is using, and the new query looks like:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN (
SELECT login_name COLLATE DATABASE_DEFAULT FROM RESOURCE
)

In addition, if the collation for one of the columns being compared is known, all the other columns can be converted to the same collation:
SELECT * FROM sys.sql_logins
WHERE NAME COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN (
SELECT login_name FROM RESOURCE
)