Join is use to combine more than two table to find result
as per our need.Join is possible when there is one match in joining table.
Types of join
6-Cartesian Join or Cross Join
Inner Join Inner Join is very important Join most of the time we use inner join.When we join two or more table with inner join it returns all the rows when there is a match in both the table.
We have two table Employee and I have inserted few records in it.
Left Join-When we Join more than two table with left join it gives all record from the left table and matching record form the right table.It means when right table matches null record or 0 record then right table return 0 record.
Right Join-When we join more than two tables with right join.It gives all record from the right table and matching from the left table.Right Join is the opposite of Left Join.If there is 0 records in the left table then left join return null records.
Full Join-When we use Full Join for combining record .It return all the record from the left table and all the records from the right table and give null for missing records.
select Employee.Name,Employee.Department,E_Salary.Salary from Employee full join E_Salary on Employee.Id=E_Salary.S_Id
Self Join is use to join the table by itself by making its alias just like virtual table.
Employee a,Employee b
Cartesian Join- When we join two or more table with cross join then it return cross product of the tables.It is also known as cross join.
Suppose you have table1 with m rows and n columns then the result o cross join is m*n rows.
CROSS JOIN E_Salary;