Joins :

->Using joins we can retrieve data from two or more tables based on logical relationships between the tables. ->using joins we can compare the data present in one table with other tables data. Joins are categorized into 4 types #Inner join #outer join *left outer join *right outer join *full outer join #self join #cross join.

Inner Join :

Inner join: In the inner join only matched records from both tables will be returned as a result.If no records are matching then result set returns nothing. NOTE: Take below given employee and training tables as base tables.
eidenameDOJSalary
1Rajesh2009-10-10 00:00:00:00018765
2Mahesh2010-05-12 00:00:00:00017889
3Rohith2011-01-01 00:00:00:00014550
4Abraham2006-02-03 00:00:00:00056850
5Tulsi2007-03-04 00:00:00:00046850
6Vamsi2009-03-06 00:00:00:00022852
7Sudheer2008-10-10 00:00:00:00028970
8Arati2012-10-10 00:00:00:000NULL
9Kavita2012-10-10 00:00:00:000NULL
Training ideidTraining nameTraining date
11CSHARP2010-10-26 00:00:00:000
21ASP.NET2010-10-26 00:00:00:000
32CSHARP2010-10-28 00:00:00:000
43SQL2010-10-29 00:00:00:000
53SQL2010-10-10 00:00:00:000
64SQL2010-10-11 00:00:00:000
72SQL2010-10-11 00:00:00:000
81CSHARP2010-10-26 00:00:00:000
119CSHARP2010-10-26 00:00:00:000
124CSHARP2010-10-26 00:00:00:000
102CSHARP2010-10-26 00:00:00:000
138SQL2010-10-24 00:00:00:000
Query to find all the training attended by each employee.Result set should contain employee names along with their training. ->select e.ename,t.trainingname from employee e inner join training t on e.eid=t.eid Query to display all the employees who have taken more than one training. ->select e.ename from employee e join trainings t on e.eid=t.eid group by e.ename having (count(t.trainingname)>1) Query to display employees who has attended the SQL training more than once. ->select e.name from employee e join trainings t on e.eid=t.eid where t.trainingname='sql' groupby e.name having(count(t.trainingname)>1)

Outer Join :

Left Outer Join :

-> In this case all the records present in the left table will be included in the result set, but only the matched result from the right side table will be included into the result set. -> When there is no corresponding match found in right side table null values are included into the result set. Ex: select e.*, t.* from emp e left outer join trainings t on e.eid-t.eid

Right Outer Join :

-> In this case all the records from right hand side table will be included in the result set, but only the matched result in the left hand side table will be included in the result set. EX:Consider the below tables,
sidNameStid
1Arun4
2Mohan4
3Mahesh4
>
stidSename
1KA
2TN
3AP
4UP
5OR
Query: select s.*,st* from students s right outer join states st on s.stid=st.stid;

Full Join :

In this type of join Data from left and right tables will be displayed irrespective of match. Ex: consider the following persons and orders1 tables
P_idlastnamefirstnameaddresscity
1kumarrambtm 1st stageBangalore
2bananjisandeep1st crossHosur
3dappumalikMG roadCB pur
Query: select p.lastname,p.firstname,o.orderno from persons p full join orders1 o on p.p_id=o.P_id orderby p.lastname.

Cross Join :

-> A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. -> Cross join behaves like an inner join if you apply the filter condition. -> We cannot use the on keyword along with cross join but we can use Where keyword. Ex: select p.*,o.* from persons p cross join orders1 o; Note : If person table is having 5 rows and orders1 table is having 3 rows, in the case cross join we will get total 15 rows in the result set when we are not using any filter condition. Ex: select p.*,o.* from persons p cross join orders1 o where p.p_id=o._id;

Self Join :

-> A self join is simply a normal SQL join that joins one table to itself. -> This is accomplished by using tabble name aliases to give each instance of the table a separate name. -> Joining a table to itself can be useful when you want to compare values in a column to other value in the same column. Ex: Consider the following products table
tidNameManufacturer
1luxHUL
2locksgodrej
3cintholgodrej
4locksITC
5ashirvadITC
Query to display the product name which are manufactured by multiple companies. -> select pi.name from products p1 inner join product p2 on p1.name=p2.name and p1.manufacturer!=p2.manufacturer;