union all 连接的两个结果集，只能最后一个结果集中允许出现order by子句，但是该order by子句对整个结果集都产生影响。
步骤一：创建测试表a,b
SQL> set autot off
SQL> create table a as select rownum as id, rownum as name from dual connect by level < 10;
Table created.
SQL> create table b as select rownum+5 as id, rownum+5 as name from dual connect by level < 10;
Table created.
SQL> select * from a;
ID NAME
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
9 rows selected.
SQL> select * from b;
ID NAME
---------- ----------
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
9 rows selected.
步骤二：测试查询语句
SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME
2 FROM a
3 LEFT JOIN b ON a.id = b.id
4
5 UNION ALL
6 SELECT a.id, a.NAME, b.id, b.NAME
7 FROM b
8 LEFT JOIN a ON a.id = b.id
9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id)
10 ORDER BY aid, bid
11 ;
AID NAME BID NAME
---------- ---------- ---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10
11 11
AID NAME BID NAME
---------- ---------- ---------- ----------
12 12
13 13
14 14
14 rows selected.
SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME
2 FROM a
3 LEFT JOIN b ON a.id = b.id
4 ORDER BY aid
5 UNION ALL
6 SELECT a.id, a.NAME, b.id, b.NAME
7 FROM b
8 LEFT JOIN a ON a.id = b.id
9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id)
10 ORDER BY aid, bid;
UNION ALL
*
ERROR at line 5:
ORA-00933: SQL command not properly ended
步骤三：检查执行计划，发现先UNION-ALL 再进行排序操作SQL> set autot on explain;
SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME
2 FROM a
3 LEFT JOIN b ON a.id = b.id
4
5 UNION ALL
6 SELECT a.id, a.NAME, b.id, b.NAME
7 FROM b
8 LEFT JOIN a ON a.id = b.id
9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id)
10 ORDER BY aid, bid
11 /
AID NAME BID NAME
---------- ---------- ---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10
11 11
AID NAME BID NAME
---------- ---------- ---------- ----------
12 12
13 13
14 14
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3394439325
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 533 | 18 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 533 | 17 (65)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 9 | 468 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | B | 9 | 234 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 1 | 65 | 10 (10)| 00:00:01 |
|* 7 | HASH JOIN ANTI | | 1 | 39 | 7 (15)| 00:00:01 |
| 8 | TABLE ACCESS FULL| B | 9 | 234 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL| A | 9 | 117 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"(+)="B"."ID")
7 - access("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement
SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME
2 FROM a
3 LEFT JOIN b ON a.id = b.id
4
5 UNION ALL
6 (SELECT a.id, a.NAME, b.id, b.NAME
7 FROM b
8 LEFT JOIN a ON a.id = b.id
9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id)
10 ORDER BY aid, bid
11 )
12 /
ORDER BY aid, bid
*
ERROR at line 10:
ORA-00907: missing right parenthesis
尝试把后面的查询语句括起来，再排序，执行计划中仍然是先Union-all再排序
SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME
2 FROM a
3 LEFT JOIN b ON a.id = b.id
4
5 UNION ALL
6 (SELECT a.id, a.NAME, b.id, b.NAME
7 FROM b
8 LEFT JOIN a ON a.id = b.id
9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id) )
10 ORDER BY aid, bid;
AID NAME BID NAME
---------- ---------- ---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10
11 11
AID NAME BID NAME
---------- ---------- ---------- ----------
12 12
13 13
14 14
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3394439325
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 533 | 18 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 533 | 17 (65)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 9 | 468 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | B | 9 | 234 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 1 | 65 | 10 (10)| 00:00:01 |
|* 7 | HASH JOIN ANTI | | 1 | 39 | 7 (15)| 00:00:01 |
| 8 | TABLE ACCESS FULL| B | 9 | 234 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL| A | 9 | 117 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"(+)="B"."ID")
7 - access("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement
转载
