Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Operators, 8 of 10
Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table 3-9 lists SQL set operators.
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.
The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:
CHAR
, the returned values have datatype CHAR
.
VARCHAR2
, the returned values have datatype VARCHAR2
.
Consider these two queries and their results:
SELECT part FROM orders_list1; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE SELECT part FROM orders_list2; PART ---------- CRANKSHAFT TAILPIPE TAILPIPE
The following examples combine the two query results with each of the set operators.
The following statement combines the results with the UNION
operator, which eliminates duplicate selected rows. This statement shows how datatype must match when columns do not exist in one or the other table:
SELECT part, partnum, to_date(null) date_in FROM orders_list1 UNION SELECT part, to_date(null), date_in FROM orders_list2; PART PARTNUM DATE_IN ---------- ------- -------- SPARKPLUG 3323165 SPARKPLUG 10/24/98 FUEL PUMP 3323162 FUEL PUMP 12/24/99 TAILPIPE 1332999 TAILPIPE 01/01/01 CRANKSHAFT 9394991 CRANKSHAFT 09/12/02 SELECT part FROM orders_list1 UNION SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP TAILPIPE CRANKSHAFT
The following statement combines the results with the UNION ALL
operator, which does not eliminate duplicate selected rows:
SELECT part FROM orders_list1 UNION ALL SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE CRANKSHAFT TAILPIPE TAILPIPE
Note that the UNION
operator returns only distinct rows that appear in either result, while the UNION ALL
operator returns all rows. A PART
value that appears multiple times in either or both queries (such as 'FUEL PUMP') is returned only once by the UNION operator, but multiple times by the UNION
ALL operator.
The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT part FROM orders_list1 INTERSECT SELECT part FROM orders_list2; PART ---------- TAILPIPE
The following statement combines results with the MINUS
operator, which returns only rows returned by the first query but not by the second:
SELECT part FROM orders_list1 MINUS SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|