Joins

Relational Database system allows you to eliminate data redundancy by structuring data in multiple tables. By adding Join conditions, data can be retrieved from multiple tables.

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 Join(Cartesian Product / cross Join) : Cartesian Join 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 Join(Equijoin / Inner Join / Simple Join) : 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.

Howmany tables can be used in a Join?

We can use a maximum of 256 tables in a join.