Define same-table, outer, and Cartesian product joins.
Same-table Joins, Outer Joins, and Cartesian Product Joins
Like the equi-join, the same-table join 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.
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.
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 imposes no such requirement.
As an example, see the SlideShow below.
Consider the above records from these truncated versions of the Customer and Order tables.
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
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.
The final join type is a type you should avoid at all costs, which is the Cartesian product of two tables.
The Cartesian product of the Customer and Order tables from the previous section is illustrated below:
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
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.
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.
Cartesian Product: The Cartesian product of two tables consists of the combination of every record in one table with every record in another table.