Practice: Using New Set Operators

Overview

This practice shows how to use the new set operators, EXCEPT, EXCEPT ALL and INTERSECT ALL.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Set up the environment

  • Execute the /home/oracle/labs/M104783GC10/setup_oe_tables.sh shell script. The shell script creates and loads the OE.INVENTORIES, OE.ORDERS and OE.ORDER_ITEMS tables.

    
    $ cd /home/oracle/labs/M104783GC10
    $ /home/oracle/labs/M104783GC10/setup_oe_tables.sh
    ...
    Commit complete.
    
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.2.0.0.0
    
    $ 

Step 2 : Test the set operator with the EXCEPT clause

  • Connect to PDB21 as OE.

    
    $ sqlplus oe@PDB21
    
    Copyright (c) 1982, 2020, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Mon Mar 16 2020 11:32:00 +00:00
    
    Connected to:
    
    SQL>
  • Count in both tables, INVENTORIES and ORDER_ITEMS, respectively the number of products available in the inventory and the number of products that customers ordered.

    
    SQL> SELECT count(distinct product_id) FROM inventories;
    
    COUNT(PRODUCT_ID)
    -----------------
                  208
    
    SQL> SELECT count(distinct product_id) FROM order_items;
    
    COUNT(PRODUCT_ID)
    -----------------
                  185
    
    SQL>
  • How many products are in the inventory that were never ordered? Use the EXCEPT operator to retrieve only unique rows returned by the first query but not by the second.

    
    SQL> SELECT count(*) FROM 
           (SELECT product_id FROM inventories
             EXCEPT
            SELECT product_id FROM order_items);
    
      COUNT(*)
    ----------
            84
    
    SQL>
  • How many products were ordered that are now missing in the inventory? The order of the queries is relevant for the result.

    
    SQL> SELECT count(*) FROM 
           (SELECT product_id FROM order_items
              EXCEPT
            SELECT product_id FROM inventories);
    
      COUNT(*)
    ----------
            61
    
    SQL>

Step 3: Test the set operator with the EXCEPT ALL clause

  • Would the usage of ALL in the set operator defined in a query in a previous step mean anything?

SQL> SELECT product_id FROM inventories
          EXCEPT ALL
        SELECT product_id FROM order_items;

PRODUCT_ID
----------
      1729
      1729
      1729
      1729
      1729
      1729
      1733
      1733
      1733
      1733
      1733
      1733
      1733
      1733
      1733
...
      3502
      3502
      3502
      3502
      3502
      3503
      3503
      3503
      3503
      3503

826 rows selected.

SQL> SELECT count(*) FROM 
       (SELECT product_id FROM inventories
          EXCEPT ALL
        SELECT product_id FROM order_items);

  COUNT(*)
----------
       826

SQL>

The result shows all rows in the INVENTORIES table that contain products that were never ordered. This does not mean anything relevant. The use of ALL in operators must be appropriate.

Step 4 : Test the set operator with the INTERSECT clause

  • How many products that were ordered are still orderable? The statement combining the results from two queries with the INTERSECT operator returns only those unique rows returned by both queries.

SQL> SELECT count(*) FROM 
       (SELECT product_id FROM inventories
           INTERSECT
        SELECT product_id FROM order_items);

  COUNT(*)
----------
       124

SQL> SELECT count(*) FROM 
       (SELECT product_id FROM order_items
           INTERSECT
        SELECT product_id FROM inventories);

  COUNT(*)
----------
       124

SQL>

Step 5 : Test the set operator with the INTERSECT ALL clause

  • Would the usage of ALL in the operator defined in the query mean anything?

SQL> SELECT count(*) FROM 
       (SELECT product_id FROM order_items
           INTERSECT ALL
        SELECT product_id FROM inventories);

  COUNT(*)
----------
       286

SQL> EXIT
$

The result shows all rows in the INVENTORIES table that contain products that were ordered. This does not mean that these products were ordered from these warehouses. The query does not mean anything relevant. The use of ALL in operators must be appropriate.