Weird 2007 left join query

[KARL DEWEY]

Operating XP.
Used the following query to concatenate fields to build a list for
comparrison --
SELECT "EBJ" & [Org] & [Sequential] AS CTK
FROM CTK_NUM;

Then ran this query for unmatched with no results.
SELECT T_Marcer_Tools.*
FROM T_Marcer_Tools LEFT JOIN CTK_Numbers ON T_Marcer_Tools.CTK =
CTK_Numbers.CTK
WHERE (((CTK_Numbers.CTK) Is Null));

Knowing there were unmatched I removed the criteria and the records without
a match showed "EBJ" for CTK_Numbers.CTK.

Ran the query below to find only unmatched.
SELECT T_Marcer_Tools.*
FROM T_Marcer_Tools LEFT JOIN CTK_Numbers ON T_Marcer_Tools.CTK =
CTK_Numbers.CTK
WHERE (((CTK_Numbers.CTK)="EBJ"));

Does 2007 allow left joins on 'partial' matches or am I missing something
fundamental?

 

[Sylvain Lafontaine]

If I remember correctly, with JET, when you have an expression such a «
"EBJ" & [Org] & [Sequential] AS CTK » in a Left Join, the calculation of the
expression is done *after* the Left Join has been performed and not before;
hence your strange result. After the Left Join, the value of [Org] and
[Sequential] are null for the unmatched rows; which will give "EBJ" as the
final result for the computation of the CTK expression.

This is different from the way most other sql engines - such as SQL-Server -
will perform such a query.

You can try replacing & with + in your expression.