Oracle da birkaç tablo üzerinde birleştirme ya da fark alma işlemleri yapılabilir. Bunun için UNION, INTERSECTION ve MINUS operatörleri kullanılır. Aşağıdaki örneklerde UNION operatörü ile birleştirilmiştir sonuçları göreceğiz.

SELECT JOB_ID

FROM EMPLOYEE

WHERE DEPARTMENT_ID = 10

UNION ALL

SELECT JOB_ID

FROM EMPLOYEE

WHERE DEPARTMENT_ID = 20

Sorgu çalıştığında çıkan 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)