UNION ALL Kullanımı
Oracle da birkaç tablo üzerinde birlestirme ya da fark alma islemleri yapilabilir. Bunun için UNION, INTERSECTION ve MINUS operatörleri kullanilir. Asagidaki örneklerde UNION operatörü ile birlestirilmistir sonuçlari görecegiz.
SELECT JOB_ID
FROM EMPLOYEE
WHERE DEPARTMENT_ID = 10
UNION ALL
SELECT JOB_ID
FROM EMPLOYEE
WHERE DEPARTMENT_ID = 20
Sorgu çalistiginda çikan sonucumuz
JOB
----------
MGR
TECH
HR
MGR
HR
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=40)
1 0 UNION-ALL
2 1 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=2
Bytes=20)
3 1 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=2
Bytes=20)
SELECT JOB_ID
FROM EMPLOYEE
WHERE DEPARTMENT_ID = 10
UNION
SELECT JOB_ID
FROM EMPLOYEE
WHERE DEPARTMENT_ID = 20
JOB
----------
HR
MGR
TECH
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=40)
1 0 SORT (UNIQUE) (Cost=8 Card=4 Bytes=40)
2 1 UNION-ALL
3 2 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
=2 Bytes=20)
4 2 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
=2 Bytes=20)
SELECT DEPARTMENT_ID, JOB_ID
FROM EMPLOYEE
WHERE DEPARTMENT_ID = 10
UNION ALL
SELECT DEPARTMENT_ID, JOB_ID
FROM EMPLOYEE
WHERE DEPARTMENT_ID = 20
ORDER BY 1
DEP JOB_ID
---------- ----------
10 TECH
10 MGR
20 MGR
20 HR
20 HR
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=40)
1 0 SORT (ORDER BY) (Cost=6 Card=4 Bytes=40)
2 1 UNION-ALL
3 2 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
=2 Bytes=20)
4 2 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
=2 Bytes=20)
