Outer Join and Inner Join

Inner Joins or Equality Join

Inner Joins are also called Equijoin, Simple Join or Equality Joins. This creates a Join through commonly named and defined column.

Traditionally WHERE clause is used to specify the relationship between different rows. Beginning with Oracle 9i, support for ANSI-compliant joins are introduced. This uses the Join keyword in the FROM clause. ANSI method has greater portability of SQL code between different DBMS platforms as most relational database are ANSI compliant. The WHERE clause is reserved for conditions that restrict the rows from being returned.

Types of Joins

• Cartesian Joins(Cartesian Product / cross Join) : Cartesian Joins replicates each row from the first table with every row from the second table. This creates a join between tables by displaying every possible record combination.
• Equality Joins(Equijoin / Inner Join / Simple Join) : Equality Joins creates a join through a commonly named and defined column.
• Non-Equality Join : Joins tables when there are no equivalent rows in the table to be joined, for example to match values in one column of a table with a range of values in another table.
• Self Join : Joins a Table to itself.
• Outer Join : Includes record of a table in output when there is no matching record in the other table.

Performance tuning relates to the activities that make the database perform more efficiently in terms of storage and access speed.