Oracle UNION / UNION ALL / INTERSECT / MINUS 之差異

剛剛閒晃看到的
先貼起來 有空再翻中文...
Oracle UNION, INTERSECT, MINUS OPERATORS AND SORTING QUERY RESULT

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.

UNION Example
The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

select empno, ename, sal, to_char(null) as “Transfer Date” from emp
UNION
select empno,ename,to_char(null) as “Sal”,tdate from oldemp;

EMPNO ENAME SAL Transfer Date

----- ----- ------ -------------

101 Sami 5000

102 Smith 11-jul-2000

201 Tamim 10-AUG-2000

209 Ravi 2400

UNION ALL Example
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

select empno,ename from emp
union all
select empno,ename from oldemp;

INTERSECT Example
The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT empno FROM emp
INTERSECT
SELECT empno FROM oldemp;

MINUS Example
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT empno FROM emp
MINUS
SELECT empno FROM oldemp;

參考網站：
Oracle 10g Database Administration