JOINS in Oracle : Obtaining Data from Multiple Tables.

Joins combine two or more tables together virtually. two tables can join if there column contains same kind of data. joining columns name may differ but their internal data specifically significant to both tables. 

Data is the preliminary requirement for database. Tables generally created to keep specific or same kind of information in it. for example in school database, student record can be group in single table namely STU_MASTER. STU_MASTER only keeps student general information where single row is reserved for each student. now consider a fee information a student can submit their fee many times, if we keep fee information with student general information it will repeat student general information many times. that's why we create different table.  

Types of Join

• Cross Join
• Equi Join
• Non-Equi Join
• Natural Join
• Join...Using
• Join...On
• Left Outer Join, Right Outer Join, Full Outer Join
• Self Join

CROSS JOIN

A Cartesian Product is formed when
1. A join Condition is omitted.
2. A join Condition is invalid.
All rows in first table are joined to all rows in the second table.

EQUI JOIN

Equi Join use equality operator(=) to join two or more tables.

NON-EQUI JOIN

Join Condition based on non equality is Non Equi Join.

NATURAL JOIN

Natural Join only use natrual join keyword to join tables. if column name is not similar in natural join then natural join works like cross join. both tables should contain at least 1 similar column name in it. 

JOIN.....USING

Natural Join does not specify joining column name. If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the using clause to specify the columns that should be used for an equijoin.

JOIN ........ ON

Specify arbitrary conditions or specify columns to join in ON Clause.

OUTER JOIN

OUTER JOIN includes some additional rows in result. Three kind of outer join is available.

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN.

LEFT OUTER JOIN
Two different ways are available for Left Outer Join. Either you can use (+) operator or you can use LEFT OUTER JOIN keyword to join tables. both syntax generate same results. 

RIGHT OUTER JOIN
Two different ways are available for Right Outer Join. Either you can use (+) operator or you can use RIGHT OUTER JOIN keyword to join tables. both syntax generate same results. 

FULL OUTER JOIN
Full Outer Join include some additional rows from both tables. full outer join must use FULL OUTER JOIN keyword to join tables.

SELF JOIN

Joining table itself is Self Join. table aliasing is used to join a table itself.