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)
