双条件连接两张表时遭遇SQL排序规则冲突与语法错误求助

Let's break down what's going on here and fix your query step by step.

First Error: Collation Conflict #

The Msg 468 error pops up because the columns you're joining on have mismatched collations — one uses SQL_Latin1_General_CP1_CI_AS and the other uses Latin1_General_CI_AS_KS_WS. SQL Server can't automatically decide which collation to use for the equality comparison, so it throws a conflict error.

Second Error: Incorrect COLLATE Placement #

Your attempt to add COLLATE at the end of the entire ON clause is invalid. The COLLATE keyword applies to individual string expressions, not the full join condition. That's why you got the Msg 156 syntax error.

Corrected Query #

You need to apply COLLATE directly to the specific column with the mismatched collation. Here's the fixed version of your query:

SELECT A.*, B.SEARCH_QUERY 
FROM Customer AS A 
LEFT JOIN LOGS_Store AS B 
ON (A.SYS_CREATED_ON = B.REQUEST_TIME)
AND (A.SYS_CREATED_BY COLLATE Latin1_General_CI_AS_KS_WS = B.USER_ID)

If both SYS_CREATED_ON and USER_ID have collation mismatches, apply COLLATE to both columns:

SELECT A.*, B.SEARCH_QUERY 
FROM Customer AS A 
LEFT JOIN LOGS_Store AS B 
ON (A.SYS_CREATED_ON COLLATE Latin1_General_CI_AS_KS_WS = B.REQUEST_TIME)
AND (A.SYS_CREATED_BY COLLATE Latin1_General_CI_AS_KS_WS = B.USER_ID)

Why This Works #

By attaching COLLATE to the column, you're explicitly telling SQL Server to use the specified collation for that comparison, eliminating the conflict. You can use either collation (whichever makes sense for your data) — just ensure both sides of the equality check use the same one.

Long-Term Fix (Optional) #

If you run into this issue frequently, you can permanently align the collations of the columns in your tables with an ALTER TABLE command. For example:

ALTER TABLE Customer
ALTER COLUMN SYS_CREATED_BY VARCHAR(50) COLLATE Latin1_General_CI_AS_KS_WS

(Adjust the data type and length to match your actual column definition.)

内容的提问来源于stack exchange，提问作者Jonathan