JOIN(INNER JOIN)

       

       A join is  a more general suset of 2 tables knowns as the cartesion product.The Cartesion product of 2 tables is another  table,consists of all possible pairs of rows columns of 1st table followed by all table columns of the 2nd table.If we Specify a two table query without a WHERE clause,the cartesion product of the 2 tables is the result.The format for cartesian product follows.

SELECT[DISTICT | ALL ]{* | colList}

FROM TABLE1 CROSS JOIN TABLE2

                                     Consider the previous example where the client and viewing tables where joined using the matching column clientNO using the database provided the Cartesian product of these 2 tables would contain 20 rows (4 client*5 viewing).

The procedure of performing join 

1. Form the cartesion product of the tables named in the FROM clause.
2. If there is a WHERE clause,apply the search condition to each row of the product table,retaining those rows that satisfy the condition
3. for each remainig row,determine the value of each item in the SELECT list to produce a single row in the result table
4. If SELECT DISTINCT has been specified,eliminate any dublicate rows from the result table.
5. If there is an ORDER BY clase,sort the result table as required

 OUTER JOINS

          The  join operation combines data from 2 tables by 

                                               forming pairs of related rows where the matching columns in each table have the same value.If one rows of a table is unmatched,the row is ommited from the result table.This is the case for inner joins or the joins that we have discussed up to now.The outer join retains rows that do not satisfy the join condition.

consider the following tables.

table name-branch1
                                                        table name-propertyforrent1

 


  The Inner Join code- SELECT b.*,p.*

                                      FROM branch1 b,propertyforrent1 p

                                      WHERE b.bcity=p.pcity;

Output is this

                                           There are no rows corresponding to the branch in Nittambuwa and no rows corresponding to the propery P14.if we want to include the unmatched rows in the result ,we can use an outer join.There are 3 types:Right,Left and full outer Join.

Left RIGHT outer Join 

                                      SELECT b.*,p.*

                                      FROM branch1 b LEFT JOIN propertyforrent1 p ON

                                      b.bcity=p.pcity;


Output may be,

                                                                includes not only those rows that have the same city,but also those rows of the left table that are unmatched with rows from the right table.The columns from 2nd table are filled with the NULLs.


                                      SELECT b.*,p.*

                                      FROM branch1 b RIGHT JOIN propertyforrent1 p ON

                                      b.bcity=p.pcity;

 

               
                                      includes not only those rows that have the same city,but also those rows of the RIGHT table that are unmatched with rows from the left table.The columns from 1st table are filled with the NULLs.



 Fill out outer Join 
                                      SELECT b.*,p.*

                                      FROM branch1 b FULL JOIN propertyforrent1 p ON

                                      b.bcity=p.pcity;

                                   includes not only those rows that have the same city,but also those rows of the   table that are unmatched in both  table.Theunmached columns are filled with null.