SQL Server: Cannot resolve the collation conflict between in the equal to operation

We get MSSQL error message: “Cannot resolve the collation conflict between “SQL_Latin1_General_CP850_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.” when run a SQL script. In order to fix the collation conflict in the equal to operation issue, we may need to add COLLATE DATABASE_DEFAULT clause to the right of column in the condition.

SQL Server Cannot resolve the collation conflict between in the equal to operation

Example to fix the collation conflict in the equal to operation

Example 1

SELECT * FROM 
tbl_profiles p INNER JOIN 
tbl_membership_card mc ON p.member_id COLLATE DATABASE_DEFAULT = mc.member_id COLLATE DATABASE_DEFAULT

Example 2

SELECT * 
FROM tbl_profiles 
WHERE member_id IN
(
	SELECT member_id COLLATE DATABASE_DEFAULT
	FROM tbl_membership_card 
)