Friday, July 24, 2009

SQL union Gotcha!

This one got me the other day. What would you expect the following to return?

SELECT 'X' AS line
UNION
SELECT 'X ' AS line


Notice the space in the second SELECT.
Well apparently SQL 2000 and 2005 both return 1 result. Even though its a UNION (and not a UNION ALL).

There is nothing I can see in Books on line about this. Why does it happen? I'm guessing it's a bug.

EDIT it's NOT a bug. See my follow up to this post for the reason why.