Tuning SQL with Joins

Oracle's Joins - Richard Earp and Sikha Bagui

There is often a need to select data from columns from more than one table. A join combines columns and data from two or more tables (and in some cases, of one table with itself). The tables are listed in a from clause of a select statement, and a join condition between the two tables is specified in a where clause.

For example, suppose we create a table called Emp with an employee number (Empno) and a job code (JobCode) as follows:

Emp

Empno JobCode

----- -------

  101      cp

  102      ac

  103      de

And then we create a second table called Job, which contains the job code (JobC) and a job title (JobTitle):

Job

JobC    JobTitle

----  ----------

  de     dentist

  cp  programmer

  ac  accountant

  do      doctor

We can use the following join command:

SELECT *

FROM job, emp

WHERE job.jobc = emp.jobcode;

This will display those resultant tuples that have jobc in Job equal to jobcode in Emp, as follows:

Empno | JobCode | JobC |   JobTitle

-----------------------------------

  102 |      ac |   ac | accountant

  101 |      cp |   cp | programmer

  103 |      de |   de |    dentist

Tuples from Job without a matching tuple in Emp are eliminated from the JOIN result. Tuples with nulls in the join attributes are also eliminated.

Cartesian Product

Joining two tables together without using a where clause will produce a Cartesian product. A Cartesian product for the above example would be:

SELECT *

FROM job, emp;

The above command says to combine all of the data in both tables and make a new table. The result would be:

Empno  JobCode  JobC    JobTitle

--------------------------------

  101       cp    de     dentist

  102       ac    de     dentist

  103       de    de     dentist

  101       cp    cp  programmer

  102       ac    cp  programmer

  203       de    cp  programmer

  101       cp    ac  accountant

  102       ac    ac  accountant

  103       de    ac  accountant

  101       cp    do      doctor

  102       ac    do      doctor

  103       de    do      doctor

Therefore, the result of a Cartesian joinwill be a relation say Q, which will have n*m attributes (where n is the number of tuples from the first relation, and m is the number of tuples from the second relation). In our example above, there is a result of 12 tuples (3×4) in the resulting set, with all possible combinations of tuples from Emp and Job. A Cartesian product can be called a JOIN with no join condition.

Oftentimes, the Cartesian product also gives data that has little meaning, and is usually a result of the user having forgotten to use an appropriate where clause in the select statement.

Equi-joins

The most common JOIN involves join conditions with equality comparisons. Such a join, where the comparison operator is '=', is called an equi-join (as in the example shown below):

SELECT *

FROM job, emp

WHERE job.jobc = emp.jobcode;

Examining the two resulting tables from above, one can see that the equi-join is actually a Cartesian product followed by a relational algebra equality selection and, in fact, the equi-join is defined in relational algebra as a Cartesian product followed by a relational algebra select (not to be confused with an SQL select). Tables can also be joined using other relational operators such as >, >=, <, <=, and <>.

Natural Join

The term "natural join" in relational algebra refers to an equi-join without the duplicate column and with the obvious join condition. If one spoke of the natural join on Emp and Job, it would produce:

Empno  JobCode    JobTitle

  102       ac  accountant

  101       cp  programmer

  103       de     dentist

The implied join condition is the equality of job code in the two tables.

Unfortunately, some authors also use the term "natural join" to mean a join between a table with a foreign key and the table that contains the referenced primary key. Because of this confusion and ambiguity, we will not refer to the natural join again in this chapter.

Joining More Than One Table

Multiple tables can be joined using join conditions. For example, if we create another table as follows:

EmpN

EmpName         Empno

John Smith      103

Sally Cox       101

George Pilcher  102

And then join the three tables as follows:

SELECT Jobc, JobTitle, Empno, EmpName

FROM job, emp, empn

WHERE job.jobc=emp.jobcode

AND emp.empno=empn.empno;

The result of the join will be:

Jobc  JobTitle    Empno  EmpName

---------------------------------------

  ac  accountant    102  George Pilcher

  cp  programmer    101  Sally Cox

  de  dentist       103  John Smith

This join is a pairwise operation. This "triple join" is actually either (EmpN join Emp) join Job, or EmpN join (Emp join Job). The choice of how the join is executed is usually made by the database's optimizer.

Outer Joins

In an equi-join, tuples without matching tuples values are eliminated from the JOIN result. For example, in the following join example, we have lost the information on the 'doctor' from the Job table because no employee is a doctor.

SELECT *

FROM job, emp

WHERE job.jobc = emp.jobcode;

Empno  JobCode  JobC  JobTitle

--------------------------------

  102       ac    ac  accountant

  101       cp    cp  programmer

  103       de    de  dentist

In some cases, it may be desirable to include rows from one table that have no matching rows in the other table. Outer joins are used when we want to keep all the tuples from the first relation, Emp, or all the tuples from the second relation, Job, whether or not they have matching tuples in the other relation. An outer join where we want to keep all the tuples from the first relation (or left relation) is called a left outer join . An outer join where we want to keep all the tuples from the second relation (or right relation) is called the right outer join. The term full outer join is used to designate the union of the left and right outer joins.

In Oracle, the (+) makes the join an outer join. Also, in Oracle, the standard left and right designations are not used. Instead, Oracle uses the terms "driving table" and "driven table" to designate the outer join. In the expression

WHERE Job.Jobc = Emp.Jobcode(+)

Job is the driving table, and Emp is the driven table. Hence, in Oracle, what is left or right is irrelevant.

A right outer join

SELECT *

FROM Emp, Job

WHERE Emp.JobCode(+) = Job.Jobc;

Empno  JobCode  JobC  JobTitle

--------------------------------

  102       ac    ac  accountant

  101       cp    cp  programmer

  103       de    de  dentist

                  do  doctor

This lists all the job codes available in the table Job, even if there are no employees using those codes in Emp yet. Here, Job is the driving table.

A left outer join

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+);

Empno  JobCode  JobC  JobTitle

--------------------------------

  102       ac    ac  accountant

  101       cp    cp  programmer

  103       de    de  dentist

This table shows all the Empno and JobCodes of the employees (from the Emp table), even if there is no corresponding JobCode or JobTitle in the Job table. Here Emp, is the driving table.

We will now look at some examples of "extended outer joins."

Outer Join with an AND

If we add an AND condition in the where clause, we produce this result:

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+)

AND Job.Jobc='cp';

Empno  JobCode  JobC  JobTitle

--------------------------------

  102       ac

  101       cp    cp  programmer

103                      de

This table purports to show all the Empno and JobCodes of the employees (from the Emp table), even if there is no corresponding JobCode or Job- Title in the Job table and where the JobCode from the Job table is 'cp'. This result turns out to be the same as an ordinary join. The effect of the outer join in the WHERE clause is not apparent because no outer join has been included in the AND clause. To correct this, when using outer joins, the (+) must also be placed in the other conditions, as shown below:

Outer join in other conditions

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+)

AND Job.Jobc(+)='cp';

Empno  JobCode  JobC  JobTitle

--------------------------------

  102       ac

  101       cp    cp  programmer

  103       de

This table shows all the Empno and JobCodes of the employees (from the Emp table), even if there is no corresponding JobCode or JobTitle in the Job table, and includes all the Empnos and Job- Codes from the Emp table including the Empno with a JobCode of 'cp'. But why did we get the other two rows with nulls? Because of the outer join in

AND Job.Jobc(+)='cp'.

This tells Oracle to include in the answer all rows where the outer join produces a 'cp' for Job.Jobc(+). No row matches Emp.JobCode= 'ac', so we get a null for that row because of the outer join. Also, no row matches Emp.JobCode='de', so we get a null row for that. There is however, a match for Emp.JobCode='cp', so that row prints < 101, cp, cp, programmer> .

Outer Joins and Nulls

Suppose we test for nulls in the outer join result table:

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+)

AND Job.Jobc(+) is null;

Empno  JobCode  JobC  JobTitle

------------------------------

  102       ac

  101       cp

  103       de

This result may be explained as follows. First, by putting a (+) on the Job.Jobc condition, we are telling Oracle to create a row for every Emp row, match or not. Then, by including

AND Job.Jobc(+) is null;

we are telling Oracle to include in the answer the rows where the outer join produces a null for Job.Jobc(+). The thing to remember is that during the process of creating an outer join, some rows match some values of JobCode, but some do not; hence, the "is null" condition is true for those cases.

Outer Join Not Included in the Null Condition

If we do not include a (+) on the null condition:

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+)

AND Job.Jobc is null;

no rows selected

Here, we are telling Oracle to create the outer join first, and then report only those rows where there is a null for Jobc in Job. Because no rows have been selected, it shows that all employees have a matching job code.

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+)

AND (Job.Jobc is null or Job.Jobc = 'ac');

Empno  JobCode  JobC  JobTitle

------------------------------

  102       ac    ac  accountant

Again, this becomes a simple equi-join because the (+) was not included in the

AND (Job.Jobc is null or Job.Jobc = 'ac');.

Outer Join with OR and IN

Changing the previous query to test the outer join condition, we might try:

SELECT *

FROM Emp, Job

WHERE Emp.JobCode=Job.Jobc(+)

AND (Job.Jobc(+) is null or Job.Jobc(+) = 'ac');

AND (Job.Jobc(+) is null or Job.Jobc(+) = 'ac');

                       *

ERROR at line 4: outer join operator (+) not

  allowed in operand of OR or IN

As shown above, an outer join is not allowed in connection with OR and IN.

Inline Views and Outer Joins

If tables would be appropriate for an outer-join query, creating an inline view as a table (with an alias — we used exp as our table alias) may be the best way to handle outer joins. For example:

SELECT * FROM

(SELECT *

FROM Emp, Job

WHERE Emp.Jobcode=Job.jobc(+)) exp

WHERE exp.jobc='cp' or exp.jobc='ac';

The output would be:

Empno  JobCode  JobC  JobTitle

------------------------------

  102       ac    ac  accountant

  101       cp    cp  programmer

Using the inline view eliminates the problem of guessing the result of added conditions because once the outer join is created, it then behaves like an ordinary table when placed in the view.

Symmetric Outer Joins

An outer join cannot be symmetric. This means that two tables may not be outer joined to each other [the (+) cannot be on both sides of the condition at the same time] in Oracle. For example:

SELECT *

FROM job, emp

WHERE job.jobc(+)=emp.jobcode(+);

WHERE job.jobc(+)=emp.jobcode(+);

                      *

ERROR at line 3: a predicate may reference only

  one outer-joined table

In this example, there is no driving table and, hence, Oracle disallows the double outer join. A way to work around this problem would be to UNION the left and right outer joins, which would produce the symmetric join.

Changing Outer Joins

As with ordinary joins, several levels of an outer join are possible. If a table Z is outer joined to a table Y, and then the outer join result is outer joined to a table X, this is known as chaining on the outer join.

Below is an example of chaining of an outer join — the table Job is outer joined to Emp, which can then be thought of as being outer joined to EmpN:

SELECT *

FROM job, emp, empn

WHERE job.jobc(+)=emp.jobcode

AND emp.empno(+)=empn.empno;

And the result would be:

Jobc  JobTitle   Empno  JobCode  EmpName  Empno

-----------------------------------------------

  ac  accountant   102       ac  George     102

                                 Pilcher

  cp  programmer   101       cp  Sally      101

                                 Cox

  De  dentist      103       de  John       103

                                 Smith

The important thing to note here is that the outer join has to be carried all the way through.

A table cannot be outer joined to more than one table at the same time as in:

SELECT *

FROM job, emp, empn

WHERE job.jobc=emp.jobcode(+)

AND emp.empno(+)=empn.empno;

FROM job, emp, empn

                     *

ERROR at line 2: a table may be outer joined

  to at most one other table

---

The above text is an excerpt from: Oracle SQL Tuning & CBO Internals ISBN 0-9745993-3-6 by Kimberly Floss