Unexpected behaviour for left join

mmadsen · #1 (**permalink**) 08-19-11, 04:48

I've come across a somewhat unexpected behavior when using custom text in a select and join statement. I use Access 2010. This is a minimal working example

TableA contains:
Id
1
2
3

TableB contains:
Id
1

Code:

SELECT A.Id, S.CustomText FROM TableA AS A
LEFT JOIN (
  SELECT Id, 'My text' as CustomText FROM TableB
) as S ON A.Id = S.Id

Expected result:
Id CustomText
1 My text
2
3

Actual result (bold is unexpected):
Id CustomText
1 My text
2 My text
3 My text

What just happened?? The curious part is that both statements below works just fine..

Code:

SELECT A.Id, S.CustomText FROM TableA AS A
LEFT JOIN (
  SELECT Id, Id as CustomText FROM TableB
) as S ON A.Id = S.Id

Id CustomText
1 1
2
3

Code:

SELECT A.Id, S.CustomText FROM TableA AS A
LEFT JOIN (
  SELECT Id, 'My text' as CustomText FROM TableB UNION
  SELECT Id, 'My text' as CustomText FROM TableB
) as S ON A.Id = S.Id

Id CustomText
1 My text
2
3

Ideas anyone?