JOINS

Joins: by using the join ,we can retrieve the data from two or more tables based on logical relationships
between the tables.


The joins allow you to view data from related tables in a single result set.we can join the more
 then one table based on a common attribute


     depending on the requirements to view data from multiple tables,we can apply the different types of joins,such
     as inner join,outer join,cross join,self join

INNER JOIN:
------------  

        An inner join retrieves the records from multiple tables after comparing the values present in a common column.when inner
join is applied ,only the rows which values satisfying the join condition  in the common column are displayed.
The rows in both the tables that do not satisfy the join condition are not displayed.

 ex:   SELECT EMP.EMPID,EMP.NAME,EMP.SAL,EPH.PHONE_NUM
FROM EMPLOYEE EMP
JOIN EMP_PHONE_NUM EPH
ON EMP.EMPID=EPH.EMPID

OUTER JOIN:
-----------

An outer join displays the result set containing all the rows from one table and matching rows from the other table.


 for example.if we create an outer join on table A and table B ,it will show you all the records of table A and only
those records from table B for which the condition on the common column holds true..


An outer join displays NULL for the columns of the related table where it does not find any matching records .
AN outer join is the following types:
1.Left outer join.
2.Right outer join.
3.Full outer join.

1.LEFT OUTER JOIN:
-------------------
THE LEFT outer join returns the all the rows from the table specified on the left side of the LEFT OUTER JOIN
keyword and the matching rows from  the table specified on the right side .it displays NULL  for the columns of the
 table specified on the right side where it does not find any matching records


EX:SELECT EMP.EMPID,EMP.NAME,EMP.SAL,EPH.PHONE_NUM
FROM EMPLOYEE EMP
LEFT OUTER JOIN EMP_PHONE_NUM EPH
ON EMP.EMPID=EPH.EMPID

2.RIGHT OUTER JOIN:
-------------------
  THE right outer join returns the all the rows from the table specified on the right side of the RIGHT OUTER JOIN
keyword and the matching rows from  the table specified on the left side .it displays NULL  for the columns of the
 table specified on the left side where it does not find any matching records


ex: SELECT EMP.EMPID,EMP.NAME,EMP.SAL,EPH.PHONE_NUM
FROM EMPLOYEE EMP
RIGHT OUTER JOIN EMP_PHONE_NUM EPH
ON EMP.EMPID=EPH.EMPID

 

 3.FULL OUTER JOIN:
-------------------
A full outer join is the combination of left outer join and right outer join.This join returns all the matching and
non-matching rows from the both the tables.
the matching records are displayed only once .In case of non-matching rows,a NULL value is displayed
for the columns for the columns for which data is not available.




ex:SELECT EMP.EMPID,EMP.NAME,EMP.SAL,EPH.PHONE_NUM
FROM EMPLOYEE EMP
FULL OUTER JOIN EMP_PHONE_NUM EPH
ON EMP.EMPID=EPH.EMPID

CROSS JOIN: A cross join is also known as a Cartesian Product .it join each row of one table with each row of the other table


      The number of rows in the result set equal to the number of rows in the first table multiplied by the number of rows
in the second table


ex:SELECT A.NAME,A.SAL,B.PHONE_NUM
FROM EMPLOYEE A
CROSS JOIN EMP_PHONE_NUM B


SELF-JOIN:  A self join, a table is  joined with itself .As a result,one row in the table correlates with other rows in the
---------   same table.


ex:SELECT E.ID,E.NAME,D.DEPT_NO
FROM EMP E
JOIN EMP D
ON E.ID=D.ID






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