Loading Consent Dialog

Database Analysis   «Prev  Next»

Lesson 4Types of joins, part 2
Objective Define same-table, outer, and Cartesian product joins.

Same-table Joins, Outer Joins, and Cartesian Product Joins

Same-table joins

Like the equi-join, the same-table join[1] is a type of inner join, so named because it creates two copies of a table and then joins their records where they have equivalent values in designated fields. The easiest way to understand a same-table join is by looking at an example. An Employee table contains records for every employee, including supervisors. If you wanted to combine every employee's record with the record of that employee's supervisor, you would use a same-table join. Consider the records in the following illustration.

Employee table consisting of columns EmpID, Name, SupervisorID

Since supervisors are also employees, you need to create two instances of the Employee table (one for employees and one for supervisors) and join them. Creating such a join would result in the following records.

A join consisting of 1) Employee instance and 2) Supervisor instance

Outer joins

One of the defining characteristics of an inner join is that its results include only records from tables having the same value in a common field. By contrast, combining the tables with an outer join[2] imposes no such requirement. As an example, see the SlideShow below.

  1. Consider the above records from these truncated versions of the Customer and Order tables.
  2. When two tables are combined by an outer join, any records from the base table with no matching records from the other table are included in the results

Oracle Outer - Joins
You would create an outer join whenever you want to list every record from one table and any matching records from another table. The outer join in the illustrations above shows information about every Customer, whether or not they have placed an order. Remember that an inner join would only return records for customers who had placed at least one order.
Visit the database concepts section of the course catalog to find out more about outer joins.

Cartesian products

The final join type is a type you should avoid at all costs, which is the Cartesian product[3] of two tables.
The Cartesian product of the Customer and Order tables from the previous section is illustrated below:
Cartesian product of the Customer and Order tables

The contents of this virtual table are meaningless. Combining a Customer record with orders from every other customer does not produce meaningful information. Also, if the source tables were sufficiently large, the act of combining them could overload your computer's memory and bring the system to a grinding halt.
Cartesian product: A type of table join: the Cartesian product of two tables consists of the combination of every record in one table with every record in another table. The next lesson discusses user views.

Create Equi Join - Exercise

Before you move on to the next lesson, click the Exercise link below to reinforce your understanding of joins.
Create Equi Join - Exercise

[1] same-table join: A type of inner join, so named because it creates two copies of a table and then joins records from the tables where the tables have equivalent values in designated fields.
[2] outer join: A link between tables in a database. When two tables are combined by an outer join, any records from the base table with no matching records from the other table are included in the results and any columns where no values are available are filled with nulls.
[3] Cartesian Product: The Cartesian product of two tables consists of the combination of every record in one table with every record in another table.