Joins and Cartesian Product in DBMS.

Joins are the queries used  to get records from two or more tables.  Joins can be of following types:

1. Equi-Joins ( = )

2.  Non-Equi-Joins (>, <, >= ,<=, !=)

Equi-Joins : Equi-joins contains: 

• Inner join (Natural join) : Join with Primary key and Foreign key.  Inner join represent by (⋈).

• Simple join : Join with Primary key and foreign key but column having same data type

• Outer join : Join with two or more table having matching records. Outer joins are follows: 

1.  Left Outer join (A B) , in Oracle it is represent by (*=).
2. Right Outer join (A B) , in Oracle it is represent  by (=*).
3. Full outer join (A B) , in Oracle it is represent by (*=*).

Non-Equi-Joins : Non-Equi-Joins use the conditional operator rather than equals.

Self Join :  A table joining with itself is known as SELF-JOIN  and it can written  as follows:

 

SELECT TABLE1.COL ,  TABLE2.COL , .......

FROM TABLE1 , TABLE2

WHERE TABLE2.FOREIGN_KEY = TABLE1.PRIMARY_KEY;

For writing a self join query, table involve in query  must have a relationship which is referring to itself.

Cartesian Product

For executing any type of join query, database performs table multiplication for specify tables enquiry which is also known as Cartesian Product. 

Once Table are multiplied then join condition specified in where clause checked and the rows which satisfied the extracted as output.

Cartesian Product of two or more table is define as each row of first table is multiply with every row in the second table , and if there are more tables than each row of Cartesian product is multiplied with next Table.

Example : Consider following two tables Employee and Department.

Table : EMPLOYEE

Table :DEPARTMENT

Table : EMPLOYEE x DEPARTMENT

Note :  In case of Natural,  the number of rows in the output will always be less than or equal to number of rows in bigger table where as other types join this condition is not true.