Define same-table, outer, and Cartesian product joins.
Same-table Joins, Outer Joins and Cartesian Product Joins
Same-table Joins: Same-table joins, also known as self-joins, are a type of join that involves joining a table to itself. This is useful when you need to compare or combine rows within the same table based on a particular condition. A common use case is when you want to compare values in different rows within the same table.
Outer Joins: Outer joins are a type of join that includes all rows from one or both tables, regardless of whether there is a matching row in the other table. There are two types of outer joins: LEFT OUTER JOIN and RIGHT OUTER JOIN. The LEFT OUTER JOIN returns all rows from the left table and matching rows from the right table, while the RIGHT OUTER JOIN returns all rows from the right table and matching rows from the left table. If a row in the left table doesn't have a match in the right table, the columns from the right table will be filled with NULL values.
Cartesian Product Joins: Cartesian product joins, also known as cross joins, create a result set that includes every possible combination of rows from the two tables being joined. This type of join is performed when there is no join condition specified in the query. The result set will have a number of rows equal to the product of the number of rows in the two tables. Cartesian product joins are not commonly used in practice because they can lead to very large result sets and are often not useful for the typical use cases of joining tables.
Now, let us look at these three types of joins in depth.
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.
1) 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.
2) A join consisting of a) Employee instance and b) 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 series of images 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 and any columns where no values are available are filled with nulls.
(The repetitive CustID and CustName fields are excluded to save space.)
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.
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. The next lesson discusses user views.
High level Overview of Oracle Joins
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Join Conditions
Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause. The join condition compares two columns, each from a different table. To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list. To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table.
Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.
Create Equi Join
Before you move on to the next lesson, click the link below to reinforce your understanding of joins.
Create Equi Join
[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 is a type of table join consisting of two tables, using the combination of every record in one table with every record in another table.
