Oracle Join Examples

This section contains the create, insert and PL/SQL code to run the examples from Chapter 4 in an Oracle 11gR2 database.

  sale_id       NUMBER NOT NULL,
  employee_id   NUMBER NOT NULL,
  subsidiary_id NUMBER NOT NULL,
  sale_date     DATE   NOT NULL,
  eur_value     NUMBER(17,2) NOT NULL,
  product_id    NUMBER NOT NULL,
  quantity      number NOT NULL,
  junk          CHAR(200),
  CONSTRAINT sales_pk     
     PRIMARY KEY (sale_id),
  CONSTRAINT sales_emp_fk 
     FOREIGN KEY          (subsidiary_id, employee_id)
      REFERENCES employees(subsidiary_id, employee_id)


INSERT INTO sales (sale_id
                 , subsidiary_id, employee_id
                 , sale_date, eur_value
                 , product_id, quantity
                 , junk)
SELECT rownum, data.*
  FROM (
       SELECT e.subsidiary_id, e.employee_id
            , TRUNC(SYSDATE
                  - DBMS_RANDOM.VALUE(0, 3650)) sale_date
            , DBMS_RANDOM.VALUE(10,10000)/100 eur_value
            , TRUNC(DBMS_RANDOM.VALUE(1,25)) product_id
            , TRUNC(DBMS_RANDOM.VALUE(1,5)) quantity
            , 'junk'
         FROM employees e
            , ( SELECT level n
                  FROM dual
               CONNECT BY level < 1800
              ) gen
        WHERE MOD(employee_id, 7) = 4
          AND gen.n < employee_id / 5
        ORDER BY sale_date
       ) data
 WHERE TO_CHAR(sale_date, 'D') 
    != TO_CHAR(TO_DATE('2012-01-01', 'YYYY-MM-DD'), 'D');

     METHOD_OPT=>'for all indexed columns', CASCADE => true);


  • The rows are inserted chronologically to reflect a natural table growth.

  • Only a small fraction of employees have sales at all.

  • No sales on Sundays. This is, however, hard to accomplish because Oracle’s TO_CHAR is sensitive to NLS_TERRITORY settings. Using TO_CHAR on both sides cancels that effect—so, it is implemented by a comparison of the weekday for a known Sunday (1st Jan 2012).

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at