From the book, Mastering Oracle SQL and SQL *Plus, Chapter 8, Apress, January 2005.

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5

8.9 Partitioned Outer Joins

We discussed outer joins in Section 8.4. This section introduces partitioned outer joins. To explain what partitioned outer joins are, let’s start with a regular (right) outer join in Listing 8-42.

SQL> break on department skip 1 on job

SQL> select d.dname as department
  2  ,      e.job   as job
  3  ,      e.ename as employee
  4  from   employees e
  5         right outer join
  6         departments d
  7         using (deptno)
  8  order  by department, job;

DEPARTMENT JOB      EMPLOYEE
---------- -------- --------
ACCOUNTING ADMIN    MILLER
           DIRECTOR KING
           MANAGER  CLARK

HR                           <<<

SALES      ADMIN    JONES
           MANAGER  BLAKE
           SALESREP ALLEN
                    WARD
                    TURNER
                    MARTIN
TRAINING   MANAGER  JONES
           TRAINER  SMITH
                    FORD
                    ADAMS
                    SCOTT

15 rows selected.

SQL>

Listing 8-42: Regular right outer join example.

The SQL*Plus BREAK command allows you to enhance the readability of query results. In Listing 8-42, we use the BREAK command to suppress repeating values in the DEPARTMENT and JOB columns, and to insert an empty line between the departments.  The result shows 15 rows, as expected. We have 14 employees, and the additional row (marked with <<<) is added by the outer join for the HR department without employees.

Look at Listing 8-43 to see what happens if we add one extra clause, just before the RIGHT OUTER JOIN operator.

SQL> select d.dname as department
  2  ,      e.job as job
  3  ,      e.ename as employee
  4  from   employees e
  5         PARTITION BY (JOB)
  6         right outer join
  7         departments d
  8         using (deptno)
  9  order  by department, job;

DEPARTMENT JOB      EMPLOYEE
---------- -------- --------
ACCOUNTING ADMIN    MILLER
           DIRECTOR KING
           MANAGER  CLARK
           SALESREP         <<<
           TRAINER          <<<

HR         ADMIN            <<<
           DIRECTOR         <<<
           MANAGER          <<<
           SALESREP         <<<
           TRAINER          <<<

SALES      ADMIN    JONES
           DIRECTOR         <<<
           MANAGER  BLAKE
           SALESREP ALLEN
                    WARD
                    TURNER
                    MARTIN
           TRAINER          <<<

TRAINING   ADMIN            <<<
           DIRECTOR         <<<
           MANAGER JONES
           SALESREP         <<<
           TRAINER SMITH
                   FORD
                   ADAMS
                   SCOTT

26 rows selected.

SQL>

Listing 8-43: Partitioned outer join example.

Listing 8-43 shows at least one row for each combination of a department and a job. Compared with Listing 8-42, the single row for the HR department is replaced with 12 additional rows, highlighting all nonexisting department/job combinations. A regular outer join considers full tables when searching for matching rows in the other table. The partitioned outer join works as follows:

1. Split the driving table in partitions based on a column expression (in Listing 8-43, this column expression is JOB).
2. Produce separate outer join results for each partition with the other table.
3. Merge the results of the previous step into a single result.

Partitioned outer joins are especially useful when you want to aggregate information over the time dimension, a typical requirement for data warehouse reporting. See Oracle SQL Reference for more details and examples.

8.10 Set Operators

You can use the SQL set operators UNION, MINUS, and INTERSECT to combine the results of two independent query blocks into a single result. As you saw in Chapter 2, the set operators have the syntax shown in Figure 8-5.

Figure 8-5: Set operators syntax diagram.

These SQL operators correspond with the union, minus, and intersect operators you know from mathematics. Don’t we all have fond memories of our teachers drawing those Venn diagrams on the whiteboard (or blackboard, for you older readers)? See also Figure 1-1. The meanings of these set operators in SQL are listed in Table 8-4.

Q1 UNION Q2

Q1 UNION ALL Q2

Q1 MINUS Q2

Q1 INTERSECT Q2

Table 8-4: Set operators.

By default, all three set operators suppress duplicate rows in the query result. The only exception to this rule is the UNION ALL operator, which does not eliminate duplicate rows. One important advantage of the UNION ALL operator is that the Oracle DBMS does not need to sort the rows. Sorting is needed for all other set operators to trace duplicate rows.

The UNION, MINUS, and INTERSECT operators cannot be applied to any arbitrary set of two queries. The intermediate (separate) results of queries Q1 and Q2 must be “compatible” in order to use them as arguments to a set operator. In this context, compatibility means the following:

• Q1 and Q2 must select the same number of column expressions.
• The datatypes of those column expressions must match.

Some other rules and guidelines for SQL set operators are the following:

• The result table inherits the column names (or aliases) from Q1.
• Q1 cannot contain an ORDER BY clause.
• If you specify an ORDER BY clause at the end of the query, it doesn’t refer to Q2, but rather to the total result of the set operator.

Set operators are very convenient when building new queries by combining the multiple query blocks you wrote (and tested) before, without writing completely new SQL code. This simplifies testing, because you have more control over correctness.

Listing 8-44 answers the following question: “Which locations host course offerings without having a department?”

SQL> select o.location from offerings o
  2  MINUS
  3  select d.location from departments d;

LOCATION
--------
SEATTLE

SQL>

Listing 8-44: MINUS set operator example.

You can also try to solve this problem without using the MINUS operator. See Listing 8-45 for a suggestion.

SQL> select DISTINCT o.location
  2  from   offerings o
  3  where  o.location not in
  4        (select d.location
  5         from   departments d)

Listing 8-45: Alternative solution without using the MINUS operator.

Note that you must add a DISTINCT operator, to handle situations where you have multiple course offerings in the same location. As explained before, the MINUS operator automatically removes duplicate rows.

Are the two queries in Listing 8-44 and 8-45 logically equivalent? Further investigations are left to the readers in one of the exercises at the end of this chapter.

You can also produce outer join results by using the UNION operator. You will see how to do this in Listings 8-46 and 8-47.

We start with a regular join in Listing 8-46. In Listing 8-47 you add the additional department(s) needed for the outer join with a UNION operator, while assigning the right number of employees for those departments: zero.

SQL> select  d.deptno
  2  ,       d.dname
  3  ,       count(e.empno) as headcount
  4  from    employees e
  5  ,       departments d
  6  where   e.deptno = d.deptno
  7 group by d.deptno
  8  ,       d.dname;

  DEPTNO DNAME      HEADCOUNT
-------- ---------- ---------
      10 ACCOUNTING         3
      20 TRAINING           5
      30 SALES              6

SQL>

Listing 8-46: Regular join.

SQL> select   d.deptno
  2  ,        d.dname
  3  ,        count(e.empno) as headcount
  4  from     employees e
  5  ,        departments d
  6  where    e.deptno = d.deptno
  7  group by d.deptno
  8  ,        d.dname
  9  union
 10  select   x.deptno
 11  ,        x.dname
 12  ,        0       as headcount
 13  from     departments x
 14  where    x.deptno not in (select y.deptno
 15                            from   employees y);

  DEPTNO DNAME      HEADCOUNT
-------- ---------- ---------
      10 ACCOUNTING         3
      20 TRAINING           5
      30 SALES              6
      40 HR                 0

SQL>

Listing 8-47: Expansion to an outer join with a UNION operator.

8.11 Exercises

The following exercises will help you to better understand the topics covered in this chapter.

The answers are presented in Appendix D.

1. Produce an overview of all course offerings. Provide the course code, begin date, course duration, and name of the trainer.
2. Provide an overview, in two columns, showing the names of all employees who ever attended an SQL course, with the name of the trainer.
3. For all employees, list their name, initials, and yearly salary (including bonus and commission).
4. For all course offerings, list the course code, begin date, and number of registrations. Sort your results on the number of registrations, from high to low.
5. List the course code, begin date, and number of registrations for all course offerings in 1999 with at least three registrations.
6. Provide the employee numbers of all employees who ever taught a course as a trainer, but never attended a course as an attendee.
7. Which employees attended a specific course more than once?
8. For all trainers, provide their name and initials, the number of courses they taught, the total number of students they had in their classes, and the average evaluation rating. Round the evaluation ratings to one decimal.
9. List the name and initials of all trainers who ever had their own manager as a student in a general course (category GEN).
10. Did we ever use two classrooms at the same time in the same course location?
11. Produce a matrix report (one column per department, one row for each job) where each cell shows the number of employees for a specific department and a specific job. In a single SQL statement, it is impossible to dynamically derive the number of columns needed, so you may assume you have three departments only: 10, 20, and 30.
12. Listing 8-26 produces information about all departments with more than four employees. How can you change the query to show information about all departments with fewer than four employees?
13. Look at Listings 8-44 and 8-45. Are those two queries logically equivalent? Investigate the two queries and explain the differences, if any.