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

MINUS

Subtracts the second query's result if they're also returned in the first query's results

Back To Top- Home - Category