|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE #temp1 (id INT, table_name VARCHAR(5)) INSERT INTO #temp1 VALUES (4,'temp1'), (1,'temp1'), (3,'temp1'), (2,'temp1'), (5,'temp1') CREATE TABLE #temp2 (id INT, table_name VARCHAR(5)) INSERT INTO #temp2 VALUES (7,'temp2'), (6,'temp2'), (8,'temp2'), (10,'temp2'), (9,'temp2') SELECT * FROM #temp1 SELECT * FROM #temp2 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
id table_name ----------- ---------- 4 temp1 1 temp1 3 temp1 2 temp1 5 temp1 (5 row(s) affected) id table_name ----------- ---------- 7 temp2 6 temp2 8 temp2 10 temp2 9 temp2 (5 row(s) affected) |
So, if we want to join the sorted #temp1 but unsorted #temp2, this won’t work.
|
1 2 3 4 |
SELECT * FROM #temp1 ORDER BY id ASC UNION SELECT * FROM #temp2 |
|
1 2 |
Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'UNION'. |
But this will work
|
1 2 3 4 |
SELECT * FROM #temp1 UNION SELECT * FROM #temp2 ORDER BY id ASC |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
id table_name ----------- ---------- 1 temp1 2 temp1 3 temp1 4 temp1 5 temp1 6 temp2 7 temp2 8 temp2 9 temp2 10 temp2 |
But it will sort everything.
In case that there is no duplicate value issue, we can use UNION ALL only if it’s ORACLE according to link.What about Microsoft?
|
1 2 3 4 5 |
SELECT * FROM (SELECT * FROM #temp1 ORDER BY id ASC) UNION ALL SELECT * FROM #temp2 |
|
1 2 |
Msg 1033, Level 15, State 1, Line 38 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. |
Meh… 🙁