Using the Set Operators

The set operators allow you to combine rows returned by two or more queries. Table 7-1 shows the four set operators.

Table 7-1: Set Operators

Operator	Description
UNION ALL	Returns all the rows retrieved by the queries, including duplicate rows.
UNION	Returns all non-duplicate rows retrieved by the queries.
INTERSECT	Returns rows that are retrieved by both queries.
MINUS	Returns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query.

You must keep in mind the following restriction when using a set operator:

• The number of columns and the column types returned by the queries must match ”although the column names may be different.

You ll learn how to use each of the set operators shown in Table 7-1 shortly, but first let s look at the example tables you ll use later in this section.

The Example Tables

You ll see the use of the products and more_products tables in the following sections. The products and more_products tables are created by the store_schema.sql script as follows :

 CREATE TABLE products (product_id INTEGER  CONSTRAINT products_pk PRIMARY KEY,  product_type_id INTEGER  CONSTRAINT products_fk_product_types  REFERENCES product_types(product_type_id),  name VARCHAR2(30) NOT NULL,  description VARCHAR2(50),  price NUMBER(5, 2)); CREATE TABLE more_products (prd_id INTEGER  CONSTRAINT more_products_pk PRIMARY KEY,  prd_type_id INTEGER  CONSTRAINT more_products_fk_product_types  REFERENCES product_types(product_type_id),  name VARCHAR2(30) NOT NULL,  available CHAR(1)); 

The products table contains the following rows. Note that I ve only shown the product ID, product type ID, and name columns because these are the only columns used in the examples. The tables contain other columns just so you can see that the tables don t have to be identical to use the set operators.

 PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- -------------------  1 1 Modern Science  2 1 Chemistry  3 2 Supernova  4 2 Tank War  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line 

The more_products table contains the following rows:

 PRD_ID PRD_TYPE_ID NAME ---------- ----------- --------------  1 1 Modern Science  2 1 Chemistry  3 Supernova  4 2 Lunar Landing  5 2 Submarine 

Using the UNION ALL Operator

The UNION ALL operator returns all the rows retrieved by the queries, including duplicate rows. The following example uses UNION ALL . Notice all the rows from products and more_products are retrieved, including duplicates:

  SELECT product_id, product_type_id, name   FROM products   UNION ALL   SELECT prd_id, prd_type_id, name   FROM more_products;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- ------------------------------  1 1 Modern Science  2 1 Chemistry  3 2 Supernova  4 2 Tank War  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line  1 1 Modern Science  2 1 Chemistry  3 Supernova  4 2 Lunar Landing  5 2 Submarine 17 rows selected. 

You can sort the rows using the ORDER BY clause and the position of the column in the two queries. The following example sorts on the first column:

  SELECT product_id, product_type_id, name   FROM products   UNION ALL   SELECT prd_id, prd_type_id, name   FROM more_products   ORDER BY 1;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- -------------------  1 1 Modern Science  1 1 Modern Science  2 1 Chemistry  2 1 Chemistry  3 2 Supernova  3 Supernova  4 2 Tank War  4 2 Lunar Landing  5 2 Z Files  5 2 Submarine  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line 

Using the UNION Operator

The UNION operator returns all non-duplicate rows retrieved by the queries. The following example uses UNION; notice all non-duplicate rows from products and more_products are retrieved:

  SELECT product_id, product_type_id, name   FROM products   UNION   SELECT prd_id, prd_type_id, name   FROM more_products;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- -------------------  1 1 Modern Science  2 1 Chemistry  3 2 Supernova  3 Supernova  4 2 Lunar Landing  4 2 Tank War  5 2 Submarine  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line 15 rows selected. 

Using the INTERSECT Operator

The INTERSECT operator returns rows that are retrieved by both queries. The following example uses INTERSECT . Notice only rows that are common to both products and more_products are retrieved:

  SELECT product_id, product_type_id, name   FROM products   INTERSECT   SELECT prd_id, prd_type_id, name   FROM more_products;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- --------------  1 1 Modern Science  2 1 Chemistry 

Using the MINUS Operator

The MINUS operator returns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query. The following example uses MINUS; notice the rows from more_products are subtracted from products and the remaining rows returned:

  SELECT product_id, product_type_id, name   FROM products   MINUS   SELECT prd_id, prd_type_id, name   FROM more_products;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- -------------------  3 2 Supernova  4 2 Tank War  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line 10 rows selected. 

Combining Set Operators

You can combine more than two queries with multiple set operators, with the returned results from one operator feeding into the next operator. By default, set operators are evaluated from top to bottom, but you should indicate the order using parentheses in case Oracle Corporation changes the default behavior in future releases.

In the examples in this section, I ll use the following product_changes table that is created by the store_schema.sql script:

 CREATE TABLE product_changes (product_id INTEGER  CONSTRAINT prod_changes_pk PRIMARY KEY,  product_type_id INTEGER  CONSTRAINT prod_changes_fk_product_types  REFERENCES product_types(product_type_id),  name VARCHAR2(30) NOT NULL,  description VARCHAR2(50),  price NUMBER(5, 2)); 

The following output shows the product_id , product_type_id , and name columns from the product_changes table:

 PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- --------------  1 1 Modern Science  2 1 New Chemistry  3 1 Supernova  13 2 Lunar Landing  14 2 Submarine  15 2 Airplane 

The next example performs the following:

• Uses the UNION operator to combine the results from the products and more_products tables.

• Uses the INTERSECT operator to combine the results from the previous UNION operator with the results from the product_changes table.

• Uses parentheses to indicate the order of evaluation, which is the UNION between the products and more_products tables followed by the INTERSECT .

    (SELECT product_id, product_type_id, name   FROM products   UNION   SELECT prd_id, prd_type_id, name   FROM more_products)   INTERSECT   SELECT product_id, product_type_id, name   FROM product_changes;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- --------------  1 1 Modern Science 

The next example changes the parentheses so that the INTERSECT is performed first; notice the different results:

  SELECT product_id, product_type_id, name   FROM products   UNION   (SELECT prd_id, prd_type_id, name   FROM more_products   INTERSECT   SELECT product_id, product_type_id, name   FROM product_changes);  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- -------------------  1 1 Modern Science  2 1 Chemistry  3 2 Supernova  4 2 Tank War  5 2 Z Files  6 2 2412: The Return  7 3 Space Force 9  8 3 From Another Planet  9 4 Classical Music  10 4 Pop 3  11 4 Creative Yell  12 My Front Line 

This concludes the discussion of the set operators.