JOINS

Joins In Oracle

       JOIN is used for selecting the data's from one or more tables.

TYPES OF JOINS::

1.EQUI JOIN
2.OUTER JOIN
   i)LEFT OUTER JOIN
  ii)RIGHT OUTER JOIN
 iii)FULL JOIN
3.SELF JOIN
4.CROSS JOIN

EQUI JOIN::

The EQUI JOIN is return the OUTPUT when the condition satisfy(Join Satisfy)returns only the "Matched Values".

Example Table::


Employees:(Table 1)
----------------------------------------------
E_Id E_Name Dept_Id Hire_date Salary
----------------------------------------------
1 Arul              1 01-Jan-11 15000
2 Xavier 2 01-Feb-10 18000
3 Vino              3 25-Sep-08 25000
4 Mathi 4 25-Dec-09 23000
5 Divya             6       05-Jan-06 45000
----------------------------------------------

Departments:(Table 2)
--------------------------
Dept_Id Dept_Name
--------------------------
1 HR
2 SALES
3 TEAM LEAD
4 DEVELOPER
5 MANAGER
---------------------------

1.EQUI JOIN QUERY::
      It return only the Matched record from the tables.

SELECT E_Id,E_Name,Hire_date,Dept_Name,Salary
FROM Employees a JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
------------------------------------------------------------
1 Arul 01-Jan-11 HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL             25000
4 Mathi      25-Dec-09     DEV 23000
-------------------------------------------------------------

2.OUTER JOIN::

The OUTER JOIN returns the Output the Common values(Matched Values ) and Unmatched Values.

OUTER JOIN QUERY::

SELECT E_Id,E_Name,Hire_date,Dept_Name,Salary
FROM Employees a OUTER JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-----------------------------------------------------
1 Arul        01-Jan-11         HR                15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino        25-Sep-08       TEAM LEAD 25000
4 Mathi 25-Dec-09       DEVELOPER     23000
5 Divya 05-Jan-06         MANAGER 45000

-----------------------------------------------------

2)i)LEFT OUTER JOIN::
The LEFT OUTER JOIN or LEFT JOIN is return the OUTPUT like Matched records and unmatched records from left side table.

LEFT OUTER JOIN QUERY::
SELECT  a.E_Id
,a.E_Name
,a.Hire_date
,b.Dept_Name
,a.Salary
FROM Employees a
LEFT JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-------------------------------------------------------------
1 Arul 01-Jan-11        HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL 25000
4 Mathi 25-Dec-09 DEV 23000
5 Divya           05-Jan-06          - 45000
-------------------------------------------------------------
2)ii)RIGHT OUTER JOIN::
The RIGHT OUTER JOIN or RIGHT JOIN returns the Output like Matched records and Unmatched records from the right side table.

RIGHT OUTER JOIN QUERY::
SELECT
E_Id
,E_Name
,Hire_date
,Dept_Name
,Salary
FROM Employees a
RIGHT JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-----------------------------------------------------
1 Arul 01-Jan-11 HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL             25000
4 Mathi     25-Dec-09 DEV           23000
- - - MANAGER -
-----------------------------------------------------

JOIN Using More than One Table::

Consider the below three table,

JOIN QUERY::
SELECT a.E_Id
,a.E_Name
,a.Hire_date
,b.Dept_Name
,a.Salary
,c.Loc_Name
FROM Employees a
JOIN Department b
ON a.Depet_Id=b.Dept_Id
JOIN Locations c
ON b.Loc_Id=c.Loc_Id;

OUTPUT::
-----------------------------------------------------------------------------
1 Arul 01-Jan-11       HR 15000 Chennai
2 Xavier 01-Feb-10 SALES 18000 Madurai
3 Vino 25-Sep-08 TEAM LEAD 25000 Tirunelveli
4 Mathi 25-Dec-09 DEVELOPER 23000 Trichy
-----------------------------------------------------------------------------

3.SELF JOIN::

SELF JOIN used for JOIN the table Itself.

Example Query::

SELECT   Emp.Employee_Id "Employee ID"
,Emp.First_Name "Employee Name"
,Emp.Salary "Employee Salary"
,Man.First_Name "Manager Name"
,Man.Salary "Manager Salary"
FROM employee Emp
INNER JOIN
employee Man
ON Emp.Manager_Id=Man.Employee_Id;

CROSS JOIN::

*It's a wrong Join (n x m)
*It wroking like all the rows in the left table will join all the row in the right table.
*It will happen if u ignore the JOIN Condition or If you provide invalid Join.
=======================================================================
JOIN NOTES::
If we are using "N" number of tables then we used (N-1) JOINs in the Query.
=======================================================================
JOIN ---USING()::

*While Using the USING() don't specify the alias name for the JOINING column.
*Only one join happens from left table to right table then(We can perform only one column joining) we use USING()

Example::

SELECT   e.Employee_Id
,e.First_Name
,Department_Id
,Department_Name
FROM employees e JOIN
Departments d
USING(Department_Id);

NATURAL JOIN::

*NATURAL JOIN also happen's only one possibilities happen to join left table to right table(only one column)
*Here also don't specify the alias for the common column.

Example::

SELECT   e.Employee_Id
,e.First_Name
,Department_Id
,Department_Name
FROM employees e
NATURAL JOIN
Departments d;
                                                    ====>JOINS END<====