Set operators
Set operators are used to join the results of two (or more) SELECT statements.
The SET operators available in Oracle 11g are UNION,UNION ALL,INTERSECT,and
MINUS.
The UNION set operator returns the combined results of the two SELECT statements.
Essentially,it removes duplicates from the results i.e. only one row will be listed for
each duplicated result.To counter this behavior,use the UNION ALL set operator which
retains the duplicates in the final result
INTERSECT lists only records that are common to both the SELECT queries; the MINUS
set operator removes the second query's results from the output if they are also found
in the first query's results. INTERSECT and MINUS set operations produce unduplicated
results.
All the SET operators share the same degree of precedence among them.Instead,during
query execution, Oracle starts evaluation from left to right or from top to bottom.If
explicitly parentheses are used, then the order may differ as parentheses would be
given priority over dangling operators.
Points to remember -
•Same number of columns must be selected by all participating SELECT
statements.Column names used in the display are taken from the first query.
•Data types of the column list must be compatible/implicitly convertible by oracle.
Oracle will not perform implicit type conversion if corresponding columns in the
component queries belong to different data type groups.For example, if a column in the
first component query is of data type DATE, and the corresponding column in the
second component query is of data type CHAR,Oracle will not perform implicit
conversion, but raise ORA-01790 error.
•Positional ordering must be used to sort the result set. Individual result set ordering is
not allowed with Set operators. ORDER BY can appear once at the end of the query. For
example,
•UNION and INTERSECT operators are commutative, i.e. the order of queries is not
important; it doesn't change the final result.
•Performance wise, UNION ALL shows better performance as compared to UNION
because resources are not wasted in filtering duplicates and sorting the result set.
•Set operators can be the part of sub queries.
•Set operators can't be used in SELECT statements containing TABLE collection
expressions.
•The LONG, BLOB, CLOB, BFILE, VARRAY,or nested table are not permitted for use in
Set operators.For update clause is not allowed with the set operators.
Advertisement
-
Union combines two different
queries into Single query.
q1:
select First_name,salary
from employees where salary in(select max(salary)from employees)
q2:
select First_name,salary
from employees where salary in(select min(salary)from employees)
Single query:
select First_name,salary
from employees where salary in(select max(salary)from employees)
union
select First_name,salary
from employees where salary in(select min(salary)from employees)
SELECT * FROM T1
C1
A
B
C
D
SELECT * FROM T2
C1
F
A
B
E
SELECT C1 FROM T1
UNION
SELECT C1 FROM T2
C1
A
B
C
D
F
E
6 rows returned in 0.00 seconds
SELECT C1 FROM T1
UNION ALL
SELECT C1 FROM T2
C1
A
B
C
D
F
A
B
E
8 rows returned in 0.00 seconds
SELECT C1 FROM T1
INTERSECT
SELECT C1 FROM T2
C1
A
B
2 rows returned in 0.01 seconds
SELECT C1 FROM T1
MINUS
SELECT C1 FROM T2
C1
C
D
2 rows returned in 0.01 seconds
