Replace:
SELECT X.Field1
,X.Field2
,Y.Field3
FROM (SELECT Field1
,Field2
FROM TABLE1) X
LEFT OUTER JOIN TABLE2 Y ON X.Field1 = Y.Field1
with:
SELECT X.Field1
,X.Field2
,Y.Field3
FROM TABLE1 AS X LEFT OUTER JOIN TABLE2 Y ON X.Field1 = Y.Field1
You don't need subquery to join two tables!
Secondly, have a look here:
Collation Precedence (Transact-SQL)[
^] to resolve collation problem/issue.