First of all, you can not order by a column that is not included in your SELECT list. Secondly, when performing a UNION query the ORDER BY clause must be a column index not a column name, because a UNION query does not have column headings (although SQL Server pretends that it has by picking the column names used in the first query although this is not ANSI compliant). Assuming you want to order the second column (A2 and B2) your query should look like this:
Code:
SELECT A1, A2
FROM TableA
UNION ALL
SELECT B1, B2
FROM TableB
ORDER BY 2
Conceptually, ORDER BY works by producing the final query table with all the queries joined together (if it is a UNION query), then it orders the query results and does not care about what is in the database.
Regards
Owain Williams