Joins in oracle database

[Adriana_andros]

Hello guys !

I need to know about the different types of joins which is being used by oracle database in the form of SQL. Can you help me establish those types of joins according to use and tell me in large about the functioning and behavior of different join. your help would be appreciated.

Thanks.

[opaper]

Joins in oracle database :

A Join is a query which helps to concatenate the rows of two or more than two tables.The Database performs the join operation when more than one tables comes up between the FROM clause with the query.

Commonly join queries carry with WHERE clause conditions which compares two different columns from different tables.This type of strategy is called as the join condition.

[MindSpace]

Equijoins in oracle database :

The equijoin is a join which comes highlighted with a join condition using an equality operator.The equijoin concatenate those rows which contains identical values for the specified columns also Depends on the internal algorithm of optimization.

The total space of the columns in the equijoin condition for a single table object may be limited to the space of a data block less some overhead and the size of the datablock is defined by the initialization parameter DB_BLOCK_SIZE.

[Modifier]

Self Joins in oracle database :

The self join is the type of join which perform the join of table to itself.The table on which joins is being performed appears two times under the FROM clause with the name of table aliases which is defined by user.This Alias redirects the column names in the join condition for join operation.

Here I am going to perform a self join for Employee table:

SELECT emp1.name||' works for '||emp2.name 
   "Employee and Their Managers"
   FROM employee emp1, employee emp2 
   WHERE emp1.man_id = emp2.emp_id
      AND emp1.name LIKE 'R%';

[Praetor]

Cartesian Products :

If two database tables for a join query don't have any join condition then Oracle database evaluate their Cartesian product.Oracle performs the concatenation of each row from one table and and other row from their respective tables.Commonly,The row generated from the Cartesian product are unusable.

Suppose,If a query performs the joining three or more tables and you left to specify a join condition there for a specific pair In case the optimizer may choose a join order which ignores the production of an intermediate Cartesian product.

[Katty]

Inner and outer Joins :

The inner join which is also called as a simple join. It combines two or more tables which evaluate the result of only those rows that satisfy the specified join condition.

The outer join expands the evaluated result of a simple join.It returns not only all rows that satisfy the join condition but also returns some or all of those rows from one table for which no any rows from the other which can satisfy the join condition.