Joins in SQL

Joins in SQL are used to retrieve data from two or more tables on the basis of a relationship between the tables. The two tables are normally related to each other on the basis of a Primary Key . Joins are classified into two main categories :

1 Inner Join : An inner join returns all the results for which the columns of the two linking tables match each other. For ex Table1 has Stu_ID , Stu_Name and Table2 has Stu_ID , DeptName . To get Dept Name for each student, we can use following querry using Inner Join.

Select Table1.Stu_Name,Table2.DeptName from Table1 Inner Join Table2 Table1.StuID=Table2.Stu_ID

This will return all the results for two tables for which the Stu_Id of the two tables match with each other. Even if the keyword Inner is not specified, it is taken as Inner Join

2. Outer Join: Outer join in SQL is further classified into two categories ....

(a) Left Outer Join : Left outer join selects all the entries for first table and only those entries of second table for which there is a match between the linked columns. If Left Outer join is applied in above case , the result will be all the entries from Table1 and only the entries from Table2 for which there is a match between the Stu_ID columns of the two columns . It is applied as :

Select Table1.Stu_Name,Table2.DeptName from Table1 Left Join Table2 Table1.StuID=Table2.Stu_ID

(b) Right Outer Join : Right outer join selects some entries for first table for which there is a match between the two tables and all the entries of second table even if there is a not a match between the linked columns. If Right Outer join is applied in above case , the result will be all the entries from Table2 and only the entries from Table1 for which there is a match between the Stu_ID columns of the two columns . It is applied as :

Select Table1.Stu_Name,Table2.DeptName from Table1 Right Join Table2 Table1.StuID=Table2.Stu_ID