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

  1. 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.
  2. 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.
  3. 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.

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.