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 shows at least one row for each combination of a department and a job. Com- pared 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 consid- ers 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.

Table 8-4. Set Operators Operator Result

Q1 UNION Q2 All rows occurring in Q1 or in Q2 (or in both) Q1 UNION ALL Q2 As UNION, retaining duplicate rows

Q1 MINUS Q2 The rows from Q1, without the rows from Q2 Q1 INTERSECT Q2 The rows occurring in Q1 and in Q2

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 with- out having a department?”

Listing 8-44. MINUS Set Operator Example

SQL> select o.location from offerings o 2 MINUS

3 select d.location from departments d;

LOCATION --- SEATTLE

SQL>

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

Listing 8-45. Alternative Solution Without Using the MINUS Operator SQL> select DISTINCT o.location

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

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 depart- ment(s) needed for the outer join with a UNION operator, while assigning the right number of employees for those departments: zero.

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;

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

SQL>

Listing 8-47. Expansion to an Outer Join with a UNION Operator 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>

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,