Set Operators

Set operators are used to combine the results of two (or more) SELECT statements. Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS. When used with two SELECT statements, the UNION set operator returns the results of both queries. However, if there are any duplicates, they are removed, and the duplicated record is listed only once. To include duplicates in the results, use the UNION ALL set operator. INTERSECT lists only records that are returned by both 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.

UNION

Returns the results of both queries and removes duplicates

SELECT  ba.authorid
FROM books b JOIN bookauthor ba
  USING (isbn)
WHERE category = 'FAMILY LIFE'
UNION
SELECT ba.authorid
FROM books b JOIN  bookauthor ba
 USING (isbn)
WHERE category = 'CHILDREN';

UNION ALL

Returns the results of both queries but includes duplicates

SELECT  ba.authorid
FROM books b JOIN bookauthor ba
  USING (isbn)
WHERE category = 'FAMILY LIFE'
UNION ALL
SELECT ba.authorid
FROM books b JOIN  bookauthor ba
 USING (isbn)
WHERE category = 'CHILDREN';

ADDING ORDER BY

SELECT  ba.authorid
FROM books b JOIN bookauthor ba
  USING (isbn)
WHERE category = 'FAMILY LIFE'
UNION ALL
SELECT ba.authorid
FROM books b JOIN  bookauthor ba
 USING (isbn)
WHERE category = 'CHILDREN'
ORDER BY authorid;

INTERSECT

Returns only the rows included in the results of both queries You might not always need to combine data rows in queries. The query in Figure 9-34 contains two SELECT statements joined with the INTERSECT set operator. The first SELECT statement asks for all customer numbers in the CUSTOMERS table—basically, a list of all customers. The second SELECT statement lists all customer numbers for customers who have placed an order recently. By using the INTERSECT set operator to combine the two SELECT statements, you instruct Oracle 11g to list all customers who have placed an order recently and who exist in the CUSTOMERS table. In other words, only customers who are retrieved in both SELECTs should be in the results.

SELECT customer#
FROM customers
 INTERSECT
  SELECT customer#
FROM orders;

MINUS

Subtracts the second query's result if they're also returned in the first query's results a list of customer numbers for customers who are stored in the CUSTOMERS table but haven’t placed an order recently. To do this, you use the MINUS set operator to remove customer numbers returned by the second SELECT statement (customers in the ORDERS table) from the results of the first SELECT statement (customers in the CUSTOMERS table).

SELECT customer#
FROM customers
MINUS
SELECT customer#
FROM orders;

Back To Top- Home - Category