->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
*left outer join
*right outer join
*full outer 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.
Take below given employee and training tables as base tables.
|Training id||eid||Training name||Training date
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,
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
|1||kumar||ram||btm 1st stage||Bangalore|
|3||dappu||malik||MG road||CB 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;
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
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;